Hi. I tried to find out if this issue was already posted, but did not find. If anyone can help me, I am trying to auto populate two fields on a subform. The field I want to populate is the description of a role. So if the user selects the role name from the combo box, the description field will automatically update. Thanks.
Auto populate fields on current form
- Thread starter dwjz07
- Start date
You could try this...
There may be a better way, but I have found that this works, but it may depend on your table structure.
See the attachment which shows my dropdown field [AdmissionWard] linked to a table. There is a "description" field beside it, because when you choose an option, only the number is displayed.
The description field is based on this formula:
=DLookUp("[RefText]","tbl Ref Values","[RefType]='Ward' AND [RefNo] = [AdmWard]").
I called this field simply "Text2". Then, in the After Update event of the lookup field, I put this code:
Private Sub AdmissionWard_AfterUpdate()
Text2.Requery
End Sub
The other thing I did was ensure that the description field has transparent borders and background.
So
1. the user makes a choice and the number is stored/displayed
2. After update DLookup formul finds the corresponding text and displays it
That works for me. I hope you find it useful.
There may be a better way, but I have found that this works, but it may depend on your table structure.
See the attachment which shows my dropdown field [AdmissionWard] linked to a table. There is a "description" field beside it, because when you choose an option, only the number is displayed.
The description field is based on this formula:
=DLookUp("[RefText]","tbl Ref Values","[RefType]='Ward' AND [RefNo] = [AdmWard]").
I called this field simply "Text2". Then, in the After Update event of the lookup field, I put this code:
Private Sub AdmissionWard_AfterUpdate()
Text2.Requery
End Sub
The other thing I did was ensure that the description field has transparent borders and background.
So
1. the user makes a choice and the number is stored/displayed
2. After update DLookup formul finds the corresponding text and displays it
That works for me. I hope you find it useful.
Attachments
allan57
Allan
- Local time
- Today, 21:29
- Joined
- Nov 29, 2004
- Messages
- 336
You can use the Column property to refer to a specific column, in a multiple-column combo box or list box. Use 0 to refer to the first column, 1 to refer to the second column, and so on. For example, in a combo box containing a column of customer IDs and a column of customer names, you could refer to the customer name in the second column as:
txtTextBox = cboCustomers.Column(1)
txtTextBox = cboCustomers.Column(1)
Thanks, for the reponse. I hate to be dumb on this but I am not following. Should I change the column property in the form on the field? If I try to do this in the table design view, it flags me that there has to be a number from 1 to 99.
Thanks for the response. However, I am not too clear with this process. In my table, I have the role name field set on the Lookup tab as combo box, and using a select table. Where would I put the string? Is that information set up in the macro builder? Thanks for your help.
ansentry
Access amateur
- Local time
- Tomorrow, 06:29
- Joined
- Jun 1, 2003
- Messages
- 995
I have posted a sample at the link below that will show you how to use a combo box to fill in multiple controls. This sample has combos filling in both Main Form and Subform. In this example the Price is saved to the table because a price in the price table may be updated and if the price was not saved then all prices in "old" invoices whould be changed. If you look at the tables (e.g. tblInvoice) you will see that only the customerID is recorded and NOT the full details of the customer.
I hope this help.
When you go to the post with the sample make sure you download InvoiceSavePrices180606
>>Click Here<<
I hope this help.
When you go to the post with the sample make sure you download InvoiceSavePrices180606
>>Click Here<<
ancentry, I took a look at your example. It is a little complex for me. I am just starting with Access so I am not that versed on the process. However, I am setting up my form somewhat similar. Although, the field for my combo box is set in the control source. The row source has select table that includes the table where I am pulling the info for the combo box and the description. Then for the After Update - Event Procedure, I am using combo box field name.SetFocus; If combo box.value > 0 then Me.Description = DLookup...
Does that make sense? This is frustrating, as I am trying to use string from other autopopulate examples. However, it is not working. Thanks.
Does that make sense? This is frustrating, as I am trying to use string from other autopopulate examples. However, it is not working. Thanks.
ansentry
Access amateur
- Local time
- Tomorrow, 06:29
- Joined
- Jun 1, 2003
- Messages
- 995
Have a look at this sample, there is not code or dlookup required. Have a look at the table tblSub and you will see that only the keys are stored and not the full details. Open the subform in design view and then have a look at the data source for the form, this will explain how the data from the tblRoles is linked to tblSub.
If you require any further help or explanation post back.
If you require any further help or explanation post back.
ansentry, I cannot ask for a more simpler example. However, I continue to struggle. I am working on this, however. Since I am not that familiar with VB and Macros, I think this is the best option for me. By the way, can I use this process autopopulating two different combo boxes on the same subform? Thanks, again, for your help.
dwjz07 said:
OK, I figured this one out.
ansentry, if the offer still stands, please see attached database. I actually chopped it up some by removing a lot of the mainform fields. I thought that would make the database more compact. Shows, how much I know about Access - huh.
OK, On the mainform (frmEmployees) are tab control with 3 tabs - subforms on each. The Security tab is the doozie. This is the one that I am having the most trouble with (actually, I'm having trouble with all), but if I understand how this one works, then I could possibly perform the auto populate on the other tabs. On the Security tab, Security field is combo box, and should populate the Security Type and Description fields. Althugh the Exception value will only populate the Security Type, Description is left as edit field. tblSecurity2 should populate with tblPeopleSoft, tblBRAC and tblException. Hope that makes sense. Again, thank you so much for taking a look at this. I hope it is not too bad.
Thanks...
neileg
AWF VIP
- Local time
- Today, 21:29
- Joined
- Dec 4, 2002
- Messages
- 5,975
You are using EmpID in tblsecurity2 as a link field but it is not populated in the table. There is therefore no data that matched ID in your main form so the subform is blank.
Just some quick points I noticed browsing your database: You should base your forms on queries, not on tables. Your object names should not contain spaces or punctuation. You have primary keys defined as 'not required', and tables with no primary keys. When you are holding a foreign key, eg EmpID in tblsecurity2, it is good practice to use the same name as it has as the primary key in the master table. I would change ID in tblEmplInfo to EmpID.
Just some quick points I noticed browsing your database: You should base your forms on queries, not on tables. Your object names should not contain spaces or punctuation. You have primary keys defined as 'not required', and tables with no primary keys. When you are holding a foreign key, eg EmpID in tblsecurity2, it is good practice to use the same name as it has as the primary key in the master table. I would change ID in tblEmplInfo to EmpID.
Last edited:
Jibbadiah
James
- Local time
- Tomorrow, 06:29
- Joined
- May 19, 2005
- Messages
- 282
Firstly, listen to Neileg all of his comments will help you to code better in future. If you then want to experiment a little try the following.
Set up your combo using the wizard (based on a query, not a table)
Insert an unbound text box called txtControl
On the "on Change" event of the combo box put in a dlookup to specify the description of the role
txtControl = DLookup("[Description]", "QueryName", "[Role] = '" & ComboBoxName & "'")
Me.Recalc
This should work... experiment with it and tell us how you get on.
J.
Set up your combo using the wizard (based on a query, not a table)
Insert an unbound text box called txtControl
On the "on Change" event of the combo box put in a dlookup to specify the description of the role
txtControl = DLookup("[Description]", "QueryName", "[Role] = '" & ComboBoxName & "'")
Me.Recalc
This should work... experiment with it and tell us how you get on.
J.
Hi Neileg, thank you for the feedback. This is new to me so bare with me, when you say "object name" should not contain spaces or punctuation, are you referring to field names? And from which form? Also, for the queries, am I just linking tables together in a query for the forms? What about the mainform, can I leave as table? Or should I run a query that has all of the fields that will be incorporated on the form? Thanks, again for your help.
Jibbadiah, thanks for the input. If you had a chance to look at my db, you will see that I have all tables attached to forms. When you say use queries, instead of tables, am I just changing these tables to queries? Thanks...
