Combo box to populate form

[sstasiak]

Hello all

As a new Access user, I'm building a database and have learned a lot up to this point. But....now I'm stuck. I have a main form(OncRegMain) that adds records to a table(tblOncReg). This table has a primary key(MEDRECNO).

What I want to do is create a search form with a combo box containing fields: MEDRECNO, LNAME, and FNAME from tblOncRegMain. When the appropriate record is selected from the combo box, I want to be able to hit enter and have the form OncRegMain open with all the fields in the form populated with the data associated with the combo box selection.

I know how to create the combo box with the appropriate columns, but have no idea what to do for the "On Enter" action.

Any ideas?

Thanks in advance

 

[Moniker]

Set the record source of the form to your table WHERE MEDRECNO = the MEDRECNO in the combobox. Have your form controls bound to the record source, and they will automatically populate.

 

[sstasiak]

Set the record source of the form to your table WHERE MEDRECNO = the MEDRECNO in the combobox. Have your form controls bound to the record source, and they will automatically populate.

I'm not sure if I'm following how to do this. Please let me know if I'm wrong:

1. In expression builder, I need to set the record source of form OncRegMain to table tblOncReg where 'MEDRECNO = comboboxMEDRECNO'

When I try to set the record source of the form OncRegMain, I get a message saying "You invoked the Query Builder on a table. Do you want to create a query based on the table?" and all it allows me to do is build a query

2. Bound form controls to the record source. Not sure how to do this

3. Once I figure out how to do the above, how do I get the populated form to open when I hit enter in the search form?

 

[sstasiak]

Here's another idea someone gave me, but I don't know if the syntax is correct because I keep getting errors. The following code should run on the "On Key Down" event to open the form with all fields populated:

If KeyCode = 13 Then  '13 is the enter key so the event only occurs when the enter key is pressed
Docmd.openform “OncRegMain”, ,,”[MEDREDNO] =  ” & me.Combobox1   'open the form where medrecno matches the record in your combobox assuming medrecno is the bound column of the combobox otherwise refer to its column
Docmd.close acform, me.form.name  'close the search form
End If

When I enter this, the following line shows up in red and gives me errors:

Docmd.openform “OncRegMain”, ,,”[MEDREDNO] =  ” & me.Combobox1   'open the form where medrecno matches the record in your combobox assuming medrecno is the bound column of the combobox otherwise refer to its column

It also highlights [MEDRECNO] on that line and says Compile Error: "Expected end of statement". Is the syntax correct or should there be quotes removed or added somewhere?

 

[Ryko]

I believe the error may be due to incorrect quotation marks. Try " instead of ”