Hi guys, Im working on a DatABase at the mo that is heavily featured around forms - the problem is I dont have a clue about how the code side of it is structured in access. I know a lot about visual basic, and I've been having pot shots at how to do this, but with no success...
How do I read the value of an object from a subform with macros? Lets say on my main form I have a textbox called 'ExampleTXT' - to read a value from the text box would be simple...
Code:msgbox Me.ExampleTXT
but on this form I also have a subform, which contains an array of textboxes (they link to a seperate table, so there can be any number of these textboxes) and they have the name 'ExampleARR'. How do I read the values from these? I have tried the following code (like I siad, these are random stabs in the dark) but I have had no success...
I am working on setting up a Document Control System and have a table, called List_of_SOP where I have, amongst others, the following fields:
- Document Title - Document Number (unique identifier) - Responsible - DL (where this is a multiline lookup column where one ticks the names of the people to whom this document needs to be distributed)
I want to be able to fill an array with the checked values of the DL field, given a particular Document Number. So far, the code is somewhat on these lines:
Dim rs1 As DAO.Recordset Dim rs2 As DAO.Recordset Dim rs3 As DAO.Recordset Dim db As DAO.Database Dim strSQL1 As String Dim strSQL2 As String
The forms in the code are subforms on a TabControl on a main form.
I have this code which needs to add together only the areas [Area] of records with a FloorNumber field value of 1 or 2 or 3 or 4.
At the moment the code works for one entry of 1 in the form frmRoomDetails. I'm guessing i need For Next or something like that but i don't know and also unsure of how to code it.
Private Sub FloorNumber_AfterUpdate() If [Forms]![frmSiteDetails]![frmRoomDetails].[Form]![FloorNumber] = 1 Then [Forms]![frmSiteDetails]![frmFloorsDetails].[Form]![Text8].Value = [Forms]![frmSiteDetails]![frmRoomDetails].[Form]![Area] End If End Sub
I know how to use openargs to pass values between forms but im not sure how to go about passing all the values from one subform to another.
To be specific I have an order form (frmCustomerOrders) that has fields like CustomerName, EmployeeName, OrderDate and so forth, the subform (frmCustomerOrdersSubform) contains information on the Products the customer is ordering which usually has multiple records with fields like ProductName, PartNumber, Quantity and ItemNumber. I want to pass the values from the CustomerOrdersSubform form to another subform (TransferSubform) My initial thought was to use something like the openform command through openargs but im not sure if that will work or if there is another method I should use.
I want to check a listbox value (the listbox is in the parent form) after updating a subform field to see if the listbox value is greater than 8.5 after updating. If it is, I want an error message to pop up and I want the field that the user is on cleared.
I think I might be referencing the control from the subform incorrectly:
This may be more a VBA question than a forms question.
I have a main form and a subform. The suform contains several fields and there are several records on it for any given record on the main form.
I also have a command button on the main form that creates a new record. I would like to have the default records to disply be based on the values from the previous subform.
I don't know if that is clear. An example the main form would contain a location and date and the subform would have sales figures for Tom, Jane and George for items a, b, and c. Tom, Jane and George would be records and a, b and c would be fields.
Generally a weeks worth of data would be entered and generally new values would be added for the same group of records from one day to the next for any given week, however, there might be times when more or less records were entered.
I would like to set it up so that the subform is populated with the same records as are in the subform before the command button is pressed to create a new record on the main form. For instance if on day one items sold are entered for Tom and Jane when a new record is created it would display Tom and Jane on the subform with blank values for items sold.
I thought I could use min or max to get a default value, but, the table that the values go into is by date so max might get the value for Tom but not Jane and George (I think).
I have this problem. I've made a table called "producers" and a main form based on this table. I've also made a table called "orders", with several data regarding orders, and a secondary table based on the "orders" table. These tables are linked. So these forms show the orders made by each producers. So, I want to show in the textbox "total" (in the footer of the sub form) the result of this function sum([kath_dik])-sum([pistosi]) for each company. [kath_dik] and [pistosi] are text boxes in the sub-form. (scroll the sub form to the right end) I tried the formula above end get an error message. Thanx in advance
Here's my situation. I have a .txt file that I need to bring into Access. This .txt file is set up with records that are separated by semicolons.
I need to pull this information into Access and separate the data by the semicolons. I can open the file in Excel and separate the information by using Data --> Text to Columns --> semicolon (as the separator).
Does anyone have an idea how I can do this into Access.
This might be pretty simple, but I haven't found anything on it (might not have looked hard enough!). What I am trying to do is scan a barcode label that I created in Access, when this is scanned, it goes to a table and opens the record that contains this ID and allows me to enter further data in this record. The further data will be data capture from a balance, which works fine if you select the location where the data is to go (using the mouse), what I need is for this location to be automatically accessed when I scan the barcode, hope that makes sense. Also, is there any way in Access to either prevent data in a table from being changed once it is entered, or alternatively to mark the data as changed/edited from the original input?
I am working on searching my customer number in my billing table and finding the appropriate customer and then pass the customer number onto my shipping table to find the corresponding shipping data to go with my billing data. My problem is this :
When I find my customer in my Billing table ( searching through field0 = CustomerNumber)and then select it, it won't pass the customer number to continue to search field0 in the shipping table for customers that have a # in any of the cell values of the recordset.
Is there a work around for special characters in cell data ie) #
Any sugestions or comments would be greatly appreciated.
I have a linked spreadsheet. Access is not pulling certain records from the field DOD. The code I used is as followsSELECT final.[SSN P ], final.TXPD, final.[TC-530], final.[TC-150], final.[ DOD ], final.[ DOB ], final.[SSN S ], final.[TC-421], final.[TC-420], final.[TC-424], final.[TC-540], final.[LFRZ-RFRZ], final.[TC-590], final.[TC-591], final.[TC-594], final.[TC-599], final.[TC-290], final.[TC-291], final.[TC-300], final.[TC-301], final.[TC-976], final.[TC-977] FROM final WHERE (((final.[TC-530])="TC-530")) OR (((final.[TC-150])="TC-150")) OR (((final.[ DOD ])="dead")) OR (((final.[ DOB ])>1929 And (final.[ DOB ])<1986)) OR (((final.[SSN S ]) Is Not Null)) OR (((final.[TC-421])="TC-421")) OR (((final.[TC-420])="TC-420")) OR (((final.[TC-424])="TC-424")) OR (((final.[TC-540])="TC-540")) OR (((final.[LFRZ-RFRZ])="-AL")) OR (((final.[TC-590])="TC-590")) OR (((final.[TC-591])="TC-591")) OR (((final.[TC-594])="TC-594")) OR (((final.[TC-599])="TC-599")) OR (((final.[TC-290])="TC-290")) OR (((final.[TC-291])="TC-291")) OR (((final.[TC-300])="TC-300")) OR (((final.[TC-301])="TC-301")) OR (((final.[TC-976])="TC-976")) OR (((final.[TC-977])="TC-977")) OR (((final.[LFRZ-RFRZ])="-ALR")) OR (((final.[LFRZ-RFRZ])="-L")) OR (((final.[LFRZ-RFRZ])="-LBR")) OR (((final.[LFRZ-RFRZ])="-LBRK")) OR (((final.[LFRZ-RFRZ])="-LR")) OR (((final.[LFRZ-RFRZ])="-LRF")) OR (((final.[LFRZ-RFRZ])="-LRKF")) OR (((final.[LFRZ-RFRZ])="-LW")) OR (((final.[LFRZ-RFRZ])="-O")) OR (((final.[LFRZ-RFRZ])="-OLR")) OR (((final.[LFRZ-RFRZ])="-V")) OR (((final.[LFRZ-RFRZ])="-VL")) OR (((final.[LFRZ-RFRZ])="-VW")) OR (((final.[LFRZ-RFRZ])="-W"));
For some reason the DOB and DOD fields are not being recognized. Please help.
I am very new to Access and I am not a programmer at all. Something in my program changed and I am stuck on how to correct it. When I look at the front end everthing looks fine. When I look at the back end one of my fields are no longer reading correctly. I get the message error code 2465
I have a command button on a Access 2010 form that i am using as my switchboard. On this form i have a hidden unbound text box that captures the users environ"username". When the user hits a command button on the form the code looks at the name in the hidden textbox that captures the environ"username" and then DLooks up a table to see if there is a match. If yes then it will open the next form and if not then a message box appears.
Code: Private Sub Command6_Click() Dim TxtUsername As String If Me.TxtUsername = DLookup("[OneLondon Login]", "TblAccessUsers") Then DoCmd.OpenForm "Bakerloo_Main_Form" Exit Sub Else MsgBox "You do not have permission to access this database" End If End Sub
This works fine apart from the fact it will only read the first name entered in the table. This table(TblAccessUsers) could have up to 50 names in it and possibly have names removed and re added at a later date. Is there a way i can get the code to look up every name in the table ??
I got a little problem wich i'm trying to solve for several days now. My situation is like this. I got a form called "Frm_File_List" and in there a subform "Frm_List_Sub" as in a datasheet. In here there are several records. Im trying that when u double click on the record that a new form "Frm_View_file" will be opened, but according the record I selected in the subform. So when I doubleclick on ID 7 it opens the form with record matching ID 7.
I tried in vba the following
Private Sub Form_Click() DoCmd.OpenForm "Frm_View_File", , , "Files_ID = Forms!Frm_List_File!Frm_List_Sub.Form!Txt_Files_ID " End Sub
But when I double click on the record it gives me this popup screen asking for the ID. When I fill in this manually then it does give me the wanted record in the form. But how do I get his ID passed automaticly?
I have a sub for which is set to sort by ascending order for a given field which happens to be a text field with vales set something like abc1.1,abc1.2 ect. The problem is, is that when you get to abc1.10, and more these are then sorted as so abc1.1 abc1.10 abc1.11 abc1.2 abc1.3 ect
How can I stop this and sort it: abc1.1 abc1.2 abc1.3 ....... abc1.10 abc1.11
my parent form has two subforms. when the user double clicks a value on one subform it populates the first blank field in the other subform. I created a code for the double click on the subform
I didnt find answer to my question!.. Hope someone here can help..
I just added security to my access application... I have added a new user and gave all admin rights. Removed all permissions from Admin and Admins group. When I log on as this new user who is equivalent of Admin,
I am able to doubleclick the table(linked table) and modify/delete the data..but when I the form is executing, it says, "Records cannot be read, no read permission on <Table>"
I have a DB with tables where the primary key fields are autoincremented, but with formatting applied.
Formatting is for example "CC"000000 so record 1 will be CC000001. (each table has a different alpha prefix)
This works fine, except the database content is being read by another application, and it sees only the 1, 2, 3 etc. Exporting the data to Excel format proves that this is due to Access not the other application.
However, if I export to Excel format, and tick the "Save Formatted" option, the data exported is as I require, CC000001, CC000002 etc.
Is there a setting or way to make Access present the data when it is read by another application to be the 'formatted' version of the data?
I am trying to connect to an excel spreadsheet and read it from access. What is the object naming scheme in excel?? I am trying to read "Sheet1" but I am getting an error. My final goal is to export a list of forms and queries list in the excel spreadsheet from one database to another. How do I know if I have "Excel 8.0"?? Here is my code:
Sub ImportForms() Dim cnn1 As New ADODB.Connection Dim rst1 As ADODB.Recordset Dim strExcelPath As String
Set rst1 = New ADODB.Recordset rst1.CursorType = adOpenKeyset rst1.LockType = adLockOptimistic rst1.Open "Sheet1", cnn1, , , adCmdTable
'Open Recordset and print test record Debug.Print rst1.Fields(0).Value, rst1.Fields(1).Value 'Clean up objects rst1.Close Set rst1 = Nothing cnn1.Close Set cnn1 = Nothing
I see a lot of requests for this operation, but apparently .DB files are so general that sometimes it's impossible? Any all-encompassing program that can read files like this? I believe they come in different formats and are produced by a variety of different programs. The only thing I ever see though are files with [.db] as the extension.
If I open the one I have with notepad on a windows machine I see some of the info but some of it is not there and is garbled.
I'm having problems trying to control default values on a continuous subform. I need to be able to have the continuous subform look up values based on a CBO on the subform, populate controls on the subform with those values, and then store the values in a table that has a foreign key linked to a control on the main form. I'm not a complete newbie at this, but for some reason I can't figure this out. The closest I've gotten to this was using a Dlookup in the afterupdate of the CBO, but that only returns one value from the table regardless of what value is selected in the control. Any suggestions?
