How to populate a form from a combo box

W

webmeister

Definitely certifiable
Local time
Yesterday, 21:07
Joined
Apr 6, 2006
Messages
107
I'm creating a form that will have a combo box and several other fields on it. From the combo box, I want to be able to select a line from the combo box, click on the line and then have the form's other fields get populated.

I know I have to have the combo box and fields tied to either a table or to an underlying query, but what might be my best way to get the form populated?

Should I, in code tied to the form, run some coded SQL based on what I select and use the SQL results, or should I use something like recordsetclone, of which I have no experince?

Thanks inadvance to thoe who respond!! I reallyappreciate your help!!
 
Access – Enter Data on Form Using Combo/List Box

With the form in design view, right click the combo/list box and select
Build Event, then Code Builder.

Select BeforeUpdate from the top right corner.

Enter the following code between the 2 existing lines.

Private Sub Combo55_BeforeUpdate(Cancel As Integer)

[fieldname] = ([Combo55],#)

End Sub

The [fieldname] is the name of the field you want to copy the data to.
Combo55 is the number of the combo box you're using.
The # refers to the column in the combo box. The columns start with number 0 (zero) in the left most column and increases by 1 for each column you move to the right. The third column from the left would therefore be column 2.

Each column you wish to copy when you select a record in the combo box needs its own line of code.

When you created the combo box using the wizard, you had the option of saving one of the columns into a field at that time. If you did this, you don't have to use the code to save that column, it’s already done.
 
statsman,

Thanks for replying....I appreciate you tking the time to do so.

Question for you: Are you certain I would tie your suggestion to the BeforeUpdate section of the combo box, and not the AfterUpdate section? I would think if I am making a selection from the Combo, your suggestion would have to be tied to an AfterUpdate function.

Thanks again for your thoughts....look forward to hearing back from you! :)
 
If there is a relationship between the tables then you can use the method in my attached sample.

If there is no relationship then post back and I will post you a sample.
 

Attachments

hi. i also have the same problem.. but in my case, when the combo box in a form is clicked, a separate form with different filled-up fields should come out. it's kind of a search function. does the same procedure apply?
 
Hi I have tried your code but I get a compile error.

Code: 
Private Sub cmbCityTest_BeforeUpdate(Cancel As Integer)
Me.City = ([cmbCityTest],1)
Me.RPostCode = ([cmbCityTest],2)
Me.RState = ([cmbCityTest],3)
End Sub
 
Its not the way I would do it (your are storing data twice which is wrong) but here is a sample that works.

"From the bottom of the country to the top"

If you want a sample of how it should be done (In my opinion)then send me a PM with an e-mail address and I will send you one with Aussie Post Codes.
 

Attachments

ansentry,

Thanks for your reply and your offer!! I haven't had a chance to check out your application yet, but plan on doing so as soon as I catch my breath. Work is hectic at the moment.

But again, thanks so much for replying.... I really appreciate it!
 
I've got a very similar question:

Same senerio except I also have a subform in my main form. I want the combo box to act similar to the record browser navigator, so that when I select an item in the combo box not only will it populate the fields on the form but also populate all the related records in the sub form. The examples given above would work without the subform, but I need to actually change records in order to update the subform.

Project Background:
Forms: frmContractors(main), sfrmContacts(sub)
Tables: tblContractors, tblContacts

frmContractors is a colmunar form that lists all the information for a single contractor (address, phone, fax, etc). sfrmContacts is a tabular subform within frmContractors that lists all the contacts at that specific company along with their info (name, duty, direct line/extension, etc).

the two tables have a relationship setup so that when a contractor is selected all the appropriate contacts are displayed. This all works fine, but instead of using the record navigator for frmContractors I want to have all the contractors listed in a combo box.

I have though of doing the main form like suggested above, then doing a SQL search for the subform display that bases off the current combo box value. However, after changing the combo box you'd need to refresh and also this won't do for adding new records.

Any suggestions?
 
I found this works to change the info on the subform to the selected combo box entry:

Set rs = Me.Recordset.Clone
rs.FindFirst "[fldWhatever] = '" & Me![Combo32] & "'"

However, I it won't change the records on frmContractors, only for the subform and also does not let me edit the Contacts in the subform. Most likely due to the relationship between the two tables and the subform and main form not matching up.

Is there a way on the combo box change event to go to the first record, then loop going through each record until the current record's key field equals the combo box text? It might be slow, but it'd be a good start.
 
Last edited:
agroom,

I think that your situation is different, if I understand you have a normal mainform subform setup. You could have 100 Contractors and each one could have say 8 contacts.

If I am correct then the above demo I posted it not for you.What you need is a combo box in the header of the main form to lookup contractors.

Have a look at the sample I have attached, a bit rough but it works.Just select and customer from the combo and it will go to that record.
 

Attachments

Hello, again, ansentry!

I took your offered sample and tried to run it after tying two tables together, but it didn't work the way I was hoping, so.... based on your offer,

"If there is a relationship between the tables then you can use the method in my attached sample.

If there is no relationship then post back and I will post you a sample.", Yes I would love your sample of non-related tables!

Thank you so very very much!
 
webmeister,

Post a copy of your database with dummy data, explain what you are trying to do and I will have a look at it for you.
 
ansentry,

Thanks, and I will take you up on your offer! However, my database, even with its data stripped down and placed into a Zip file, is still 1.5 mB in size. If you can PM me with your e-mail address, I could then send you the database.

Here's a little explanation of what you can expect:

In the database, I have a form titled Data Input Form. When you open it, you will see that I am trying to pick a customer, and have that customers first related data record display. That customer might have more than one record at which point, I would then use the left - right arrows at the bottom of the screen to scroll through the customer's multiple records.

I'm thinking that perhaps I should involve a form and subform, but just am not sure. I'm also thinking the defects list on my form is not the way to go...that it should be less conspicuous, so to speak.

In any case, whatever you might be willing to assist with is definitely appreciated, and I look forward to your reply!
 
You must log in or register to reply here.

Similar threads

tmd63
How to use a combo box to filter a forms data
Replies
4
Views
327
Pat Hartman
P
J
Auto-Filling 2 Form Fields
2
Replies
32
Views
1,287
Gasman
Gasman
J
Combo Box to filter and Allow Edits
Replies
4
Views
477
Pat Hartman
P
L
Continuous form with different rowsource in same combobox
Replies
4
Views
222
linux_s2002
L
A
Search box to find parts to add to job instead of a combo box
2 3
Replies
41
Views
885
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom