Modules & VBA :: Reference Field On Form
Apr 21, 2015
I had this line :
GBL_Tab05ID = Me.Tab05ID
and wanted to change this. I have this on the On Current event and it gives me an error because the data is not in the record source when the form opens. How can I change the me. to the formname? The form gets dynamically created so i have
Dim CurrentForm As String
CurrentForm = Me.Form.Name
then i put
GBL_Tab05ID = Forms(CurrentForm).Form.Tab05ID
but that did not work...what is the correct syntax?
View Replies
ADVERTISEMENT
Jun 2, 2014
I have placed an unbound subform on a form. I have the following code which loads in different datasheet to the subform:
'Loads in NewQuery
Me.data.SourceObject = "query.NewQuery"
'Loads in NewQuery2
Me.data.SourceObject = "query.NewQuery2"
etc.... this works fine to show these queries but i want to know how to reference the fields on the datasheet.
for example one of the fields in NewQuery is "ID"... When clicked i want to run code... normally on bound subform i could go for example ID_Click()... msgbox(me.ID)
how can i reference these other fields on unbound???
View 3 Replies
View Related
Jun 18, 2015
I want Access to automatically generate a reference number for a record based on the values in on two other fields for a given record using a form.
The first field is called Operation Number.
The second is Bag Number.
The reference number needs to be in this format: 19C.3.1
Where 19C is the Operation Number, 3 is the bag number, and 1 is automatically generated. Additionally I need the last number --the automatically generated one--to go back to 1 if with each new bag number.
This is kind of like library catalog numbers. Not sure how to do this.
View 2 Replies
View Related
Mar 25, 2006
Hi All
I am trying to reference a field on a sub form from a button on the main form. I want to read a field called "Date1" and I want Date1 from the first row, regardless of which row has been selected by the user. The statment used is
FrmGroupSubform.Form!TxtDate1.
Any ideas PLEASE
Steve
View 2 Replies
View Related
May 8, 2014
I have form named SearchForm and there is unbound subform named Search_blank. Depending on button in menu is used source object of Search_blank. One of the source objects is form named MeasureForm and it has subforms named MeasureForm_sf1 and MeasureForm_sf2. How can I reference public sub in MeasureForm_sf1 from SearchForm? How can I reference public sub in MeasureForm_sf1 form MeasureForm_sf2?
There might be a problem with unbound form. I tried reference public sub named GetLastVal from SearchForm that is in MeasureForm:
Code : Me!Search_blank.Form.GetLastVal
and it was working. Than I tried reference public sub named GetLastVal2 from SearchForm that is in MeasureForm_sf1:
Code : Me!Search_blank.Form!MeasureForm_sf1.Form.GetLastVal2
and I got RunTime error 2465, cannot find MeasureForm_sf1
View 4 Replies
View Related
Sep 9, 2013
I have a form with 10 combo-boxes. Once user selects a value in CB1- CB2 becomes visible and active. I am trying to run a dynamic query- where the selection of the combo-box is used to select a column from my table called "dbo_animals"
To elaborate: CB1 contains the following values
Code : elephant, giraffe, bufffalo, tiger, lion
Once the user selects elephant in CB1, CB2 becomes active and I select tiger next. So the query becomes like. The process can go on and the user can select up to 10 animals
Code : **SELECT elephant, tiger FROM dbo_animals**
Problem: I am able to create the query with string manipulation- Unfortunately due to the way the loop through controls is set up- The query is unable to convert the text into a reference. If I hard code it as
tempquery = "SELECT [Form]![Animal Finder]![CB1] FROM dbo_animals" MsgBox(tempquery)
It looks like
Code : SELECT Elephant from dbo_animals
This is how I want. But, since i am looping through CB controls, I have it set up as
Code : tempquery = "SELECT" & " [Form]![Animal Finder]![CB" & i & "] FROM dbo_animals"
and this shows up as
Code : SELECT [Form]![Animal Finder]![CB1] FROM dbo_animals
Thereby giving me an error saying that the reference is not valid and asking me for a parameter value.
This makes sense, since it is unable to evaluate the text as a reference.
How do I correct the text into a reference? Or How do I build a query with adjusting columns based off selections from a combo-box and loop through the comboboxes in the form while auto-updating the query?
View 4 Replies
View Related
Dec 4, 2005
hi
how do i reference a field on the parent form from the child form e.g.
i have a button on the child form which needs to read a value on the parent form and use it i na calculation etc
thanks
View 1 Replies
View Related
Aug 14, 2015
I have inherited maintenance of an Access DB that I did not design and I'm having trouble with their Navigation Form setup. I created a form with a list box and two date fields, and figured out how to build a filter for the form that would open a specified report on a button click. It worked great as a stand-alone form, but when I added it to a navigation control in a navigation form I can't seem to find the right path to tell the VBA where to look for the filter criteria.
In the screen shot, the report to be displayed is in the detail of the "Report Center" tab on the far right of the navigation form "Main." the form "FilterSelection" is inserted in the "NavigationControl0" section under the navigation tabs. Criteria is selected/entered in "FilterSelection" and needs to be passed to the filter of the report that is opened when the "Project Tasks" navigation button is pressed. As I mentioned, when I created the form I put a button on it to open the form with the filter (built via SQL in VBA) it worked beautifully. But now that it's on the "NavigationControl0" object Access errors and can't find the fields where the data has been selected/entered.
View 8 Replies
View Related
May 24, 2006
I am working on an form (using Access 2000 and VBA) that stores registration information, along with events that were attending, in a table and later retrieves that same information from the table to display the regist. info, events attended, and allows the user to change any current info along with adding new/recently attended events to each record.
I am currently allowing a max of six events, along with event details, to be stored and I am running through six different if statements to match the Event_List (name of the combo box that contains "Event 1 Event 2...Event 6") to the appropreiate information in the table. I have started to simplify the code to remove the redundancy by running a Do While to to find the correct Event by using an integer variable that starts at 1 and is incremented until it matches the 7th character (the number portion) of the Event chosen from the Event_List. After this I want to use that integer variable to say what event information in the table to gather. My problem is this: I have tried many different varieties of the following code and I am unable to make it work. I had never used Access up until a week and a half ago, and a few days ago I went and bought the book "Access VBA Programming for Dummies" but it doesn't help for this problem. Here's part of the code:
Variables (Event_Name, PMT, Receipt, Payment_Type, PIF) refer to Form textboxes while the variables in the [] refer to table field names.
'------Code I'm Currently Working On-------
Dim intX As Integer
Dim EventVal As Integer
Dim strX As String
intX = 1
EventVal = Val(Mid(Event_List, 7, 1))
Do While EventVal <> intX And intX < 6
intX = intX + 1
Loop
strX = str(intX)
Dim E_Name As String
Event_Name = [Event strX ]
PMT = [PMT strX]
Receipt = [Receipt strX]
Payment_Type = [Payment Type strX]
PIF = [Paid In Full strX]
'-----Old Code That I'm trying to simplify-------
If Event_List = "Event 1" Then
Event_Name = [Event 1]
PMT = [PMT 1]
Receipt = [Receipt 1]
Payment_Type = [Payment Type 1]
PIF = [Paid In Full 1]
Camp_Site = [Camp Site 1]
Cabin_Number = [Cabin Number 1]
ElseIf Event_List = "Event 2" Then
Event_Name = [Event 2]
PMT = [PMT 2]
Receipt = [Receipt 2]
Payment_Type = [Payment Type 2]
PIF = [Paid In Full 2]
Camp_Site = [Camp Site 2]
Cabin_Number = [Cabin Number 2]
ElseIf Event_List = "Event 3" Then
Event_Name = [Event 3]
PMT = [PMT 3]
Receipt = [Receipt 3]
Payment_Type = [Payment Type 3]
PIF = [Paid In Full 3]
Camp_Site = [Camp Site 3]
Cabin_Number = [Cabin Number 3]
PLEASE HELP!!!!
View 3 Replies
View Related
Mar 25, 2014
In my form (source tblJobs) i input a contract number from tblContracts. but i would also like it to show the contract address when the contract number is entered. Both fields belong to tblContracts but i only wish to enter the contract number, the address will just be used as a reference as it isnt something i would have to enter again.
So for example
112 - Main Road
If i enter 122 in my ContractNo field i would like it to show the contract address for that specific contract.
Can i do this? If So how?
View 5 Replies
View Related
Oct 22, 2013
I have a form which have a sub form. In the sub form I have a the direct table to update data on it.
Example:
The form has the name of the project and the subform the name of one of the items of the same project and the table present the documents for the particular item.
The users have the option to add or edir docs to this item.
If I would like to reffer to a value they are entering on this table in VBA . how can I do that?
(I would like to present a msgbox when they are entering a date that isn't right on the table for this item.)
By the way, I can't use validation rule because I have a different dates needed per item.
View 2 Replies
View Related
Jul 16, 2013
reference a text box in sub form. I did the same thing for the combo box in it worked perfect. I am not sure why this is not working for textbox.
This is how I referenced it:
[Forms]![MainOrderForm]![CuttingNumForm].[Form]![Style1]
Form: MainOrderForm
Sub Form: CuttingNumForm
Text Box: Style1
I should also mention that "Style1" filed in automatically once a "CuttingNum" field is selected.
View 8 Replies
View Related
Mar 5, 2015
I have a form with a subform. I would like a control in my parent form to uncheck a checkbox control in my subform when that field value is deleted . I can get that to work if there is only one record in my subform but it doesn't uncheck the rest. how to I reference all the records in my subform so all of them will uncheck when I delete that field value?
View 2 Replies
View Related
Aug 17, 2007
I have a large table that is appended daily. I need to be able to specify two dates, a start and an end, and pull only the records in the table with those dates. There will be two records for each account number, one from the specified start date and one from the specified end date. I then need to calculate the difference between a value in a single field from the end date and a value from the same field from the start date. DLookUp will not work because there is an input parameter. How could I accomplish this task?
View 2 Replies
View Related
Feb 26, 2005
I have 10 fields on a subform. they are named value1 to value10 consecutively. I would like to loop thru them, and get their values to use in subsequent events. I tried the following to reference them...
Dim iLoop as integer
Dim fldVal as control
Dim ItemValue as string
For iLoop = 1 to 10
fldVal = ("Forms![Form 1]![Subform 1]!value" & iLoop)
ItemValue = fldVal
Next iLoop
I also tried a couple of other variations, but cannot get this **** thing to work. Any help would be appreciated.
View 2 Replies
View Related
Apr 6, 2005
I have a form bound to a query. The query result has a field that I would like to reference, however I do not want the field on my form. Is there a way to reference a field in the query results that is not on the form?
View 3 Replies
View Related
Dec 15, 2014
whatever Microsoft office program is open. The problem i get is that when i add the reference Microsoft outlook 14.0 object library i get a compile error "ByRef argument type mismatch".
Code:
Option Compare Database
Public Enum OfficeAppName
Outlook = 1
PowerPoint = 2
excel = 3
Word = 4
Publisher = 5
Access = 6
End Enum
[code]....
View 4 Replies
View Related
Feb 15, 2014
I have a form with 3 levels.
main - [Welders fm],
2nd level - [PipingQualificationTickets tbl Subform]
3rd level - [WelderWps fm] This is a pop up form but is related to the 2nd level formVBA Error
I have a dropdown box [Wps] (3rd Level PopUp) with an after update function to lookup a query [DLookUpWeldWPS Qy] and insert the text into [Wps(s)QualifiedFor] on the 2 level [PipingQualificationTickets tbl Subform]
Here is the code,
Forms![Welders fm]![PipingQualificationTickets tbl Subform].Form![Wps(s)] = DLookup("[Wps(s)QualifiedFor]", "[DLookUpWeldWPS Qy]", "[WelderWps tbl]![QualificationID] = " & Nz(Forms![Welders fm]![PipingQualificationTickets tbl Subform].Form![WelderWps fm]![QualificationID], 0))
I am getting an error - You entered an expression that has an invalid reference to the property form/report
The names are all correct to the forms.
View 2 Replies
View Related
Oct 10, 2013
I have a main form "KZ_SEARCH" and within this I have a subform which is loaded when a button is pressed and is populated with query results (based on text entered in a text box). The subform is called "KZ NOTICES Query subform". That all works fine and the query modifies based on the text entered every time the button is pressed. The next stage is that when the subform is loaded I want the user to be able to select a line from the subform and to jump to that record in another form (which also needs to be opened from that selection). The reference for the record to select in the newly opened form would be the first column of the selected line highlighted in the subform (control name "KZ NUMBER").
View 10 Replies
View Related
Jul 8, 2015
Which reference to be added to use Recordset object in Excel VBA. I added "Microsoft DAO 3.6 Object Library" reference but still its not working.
View 8 Replies
View Related
Dec 5, 2013
When entering a lookup reference to a table field it pop up message saying, "you can't modify the table 1 this time as it is in use by other person or process" i have closed all forms and table reports but the problem is same to rectify ? also when going to see the realationship then there shows no relation ship developed even after the lookup reference from one table filed to another table field.
View 1 Replies
View Related
Apr 10, 2013
I have two tables, Table 1 and Table 2 and two related forms, Frm1 and Frm2. I want to be able to update the data on Table1 from Frm2, that is, if I change the data on Frm2, the corresponding fields in Table1 will update to the same data. I know that if both tables are open I can reference the fields on Frm1 and Frm2 with an IF statement: If Active = True And Forms!Frm1a.DoNotchange = False Then..And I know on the update query I can reference a control on a form by Forms!Formname.Control. Wll works fine.What I would like to know is can I reference a table field in the same way. For example, I have tried to use the IF Statement If Active = True And Tables!Table1.DoNotchange = False Then..Is there a way to reference the Table instead of the Form in both the VBA and the query? The problem seems to be that I have the two bound controls [Active] on Frm2 and [DoNotChange] on Frm1. (Which I need).
View 5 Replies
View Related
Jul 29, 2013
I am trying to automate the generation of a reference number incrementally by 1.
In my main table (tblBooking) I have my primary key field autBookingID which is an autonumber.
In another table (tblBookingStops) I have the primary key as autBookingStopsID as the autonumber and then numBookingID linked to the above table (tblBooking). I have another field called txtGPSID so this is the number I would like to automate.
So for example:
tblBooking:
autBookingID: 1234
tblBookingStops:
[Code]....
I want to put the code that would populate the txtGPSID on a form in datasheet view and would like to put the code on the load event.
View 5 Replies
View Related
Nov 21, 2013
I'm trying to put together a DB for creating notifications to customers. The source file is pulled out from another system as an Excel file, which is then uploaded to Access via VBA. The user runs a query, and then sends the results (customer information) to an Excel file where we have set up a mail merge document (the notification itself).
A customer may appear in multiple records (having multiple accounts), but will only receive one notification.
What we're trying to do is give each notification an unique ID that will become its document reference number once the document is created. The number will need to be the same for all the records in which the customer appears, and they will need to be consecutive, as the documents themselves will be stored in PDF form later on in the process.
So I would like to assign an unique ID to each customer, each time it shows up when querying the uploaded source file (the query is done in a form, that has a sub-form for showing the results). Then, when exporting the query results, they will be also copied and stored to another table, along with the assigned reference number, for monitoring purposes.
The Access file itself will be split and used by multiple users (up to 9 users at a time). In theory, each user will see their own customers (each customer account is assigned to a certain user), but the number assignation will need to also depend on when the query has been run. For example, if user 1 queries the DB at 9:30AM they will get records numbered 1000 to 1050; user 2 (who queries the DB at 9:31AM) gets numbers 1051 to 2100 etc.
View 2 Replies
View Related
Jun 15, 2006
Can anyone help?
I have few of these columns in my query
Loc Company Pack RoundedNum SumTotal
2 ASDA AA 1
2 ASDA AA 5 6
1 Asda AC 2 2
1 ABC BB 10
1 ABC BB 1 11
2 XYZ AR 1 1
I am trying to achieve "SumTotal" column result. In the above query RoundedNum is an expression achieved from other columns(not shown above) from the same querytable. Now I want to insert this "SumTotal" column which will count for Total RoundedNum as per same type of Packs.
Thus the Total Field should look like the one shown above. Any idea how can i achieve this? :confused:
Thank you in advance.
View 2 Replies
View Related
Mar 15, 2006
I would like to update two fields [Category] and [ProdType] in tblAccum based on a reference table.
The reference table is tblReference and contains the fields [Code], [Category] and [ProdType].
tblReference example of field values:
Code Category ProdType
A Blank Accessory
BS Blank Blank Stock
O Printed Offset
So if the Code field in tblAccum has a value of O then based on the tblReference table the Category value would be Printed and the ProdType value would be Offset.
Any help is greatly appreciated.
Thanks,
View 7 Replies
View Related
