How to make combo box populate subform?

[joner89]

Hey guys, first of all i am a student. I have done some work on databases using Oracle but im completely new to Access so please bare with me. Also im nor sure if im in the right forum but here is my issue.

I have a subform called product catalogue. It links to a form that is populated by a table containing productID,description,price etc.

I have created a combo box that displays all of the product information from the table for each record at a time e.g 1(productID),Spanner(description),4.18(price) etc.

What i want to do is when the user goes through the combo box and chooses the product they want, once they select it all of the information from the combo box goes to its cooresponding cell in the sub form.

So for example is they chose 1,Spanner,4.18. The boxes in the form would populate: ProducctID - 1, Description - Spanner, Price - £4.18.

I have a VBA access for dummies book and i found a section that i thought would help me. I used the following code which should have worked for what i want. Here is the code.

Private Sub GoToProduct_AfterUpdate()
'clone the forms table into a recordset.
Dim MyRecSet As Object   'declare object
Set MyRecSet = Me.Recordset.Clone   'clone Product Table
 
'find first matching record in the recordset.
MyRecSet.FindFirst "[ProductID]" = " & Me![GoToProduct]"
 
'Set the forms record to found record
Me.Bookmark = MyRecSet.Bookmark
End Sub

However the combo box still doesnt populate the form for me. Is there anyway i can do this?

Thanks in advance!

 

[Trevor G]

Welcome to the Forum,

Is the sub form built around a query if so then set the criteria in the query to use the Combo Box, it will then find the records.

 

[joner89]

Hi Trevor G

Thanks for your welcome and reply.

Im not sure what you mean when you aks if it is built around a query?

I have built the subform using the wizard from an existing table (this contains the list of all the information).

'set the criteria in the query to use the Combo Box, it will then find the records.'

Im sorry my knowledge is very basic and i am not quite sure what you mean by this.

Thanks for your help so far though!

 

[Trevor G]

Open the subform in design view.
Open the properties of the form
In the record source select the elipsees button on the right this will invoke the Build Wizard and it asks you to Do you want to create a query based on the table. Click Yes
Then select all the fields.
Save the query with a name something like frmSubProducts
Then select the criteria of the field which will match crom the combo box list. Next use the build wizard icon to then select the form and combo box.
Save and close out of the query.
Close the form
Next Open the form which has the combo, select something and the subform should filter accordingly.

If you have issues then upload a sample of the database.

 

[joner89]

Thanks Trevor G!

I kind of worked it out myself. I had a hint from my lecturer with the following code.

Private Sub GoToProduct_AfterUpdate()
    If GoToProduct.Value <> "" Then
        ID.SetFocus '..Set the focus to the field returned by the combo box
        DoCmd.FindRecord GoToProduct.Value, acAnywhere, False, acSearchAll, False, acCurrent, True
    End If
End Sub

Turns out all i had to do was change the line ID.SetFocus to a field on my form. In this case 'productID'. It works fine now and populates the form with the data from my combo box!

Thanks for your help!

 

[Trevor G]

Good to read you have this resolved.

 

[dannynguyen1980]

Thanks Trevor G!

I kind of worked it out myself. I had a hint from my lecturer with the following code.

Private Sub GoToProduct_AfterUpdate()
    If GoToProduct.Value <> "" Then
        ID.SetFocus '..Set the focus to the field returned by the combo box
        DoCmd.FindRecord GoToProduct.Value, acAnywhere, False, acSearchAll, False, acCurrent, True
    End If
End Sub

Turns out all i had to do was change the line ID.SetFocus to a field on my form. In this case 'productID'. It works fine now and populates the form with the data from my combo box!

Thanks for your help!

Thank for sharing!
Will this code works well when using databases managed by SQL Server 2000?
My case is:
I got a main form and a subform in which the field CustomerID (data source: table Customer) on the main form populate subform contains following fields: Invoice No., Date, Description, Quantity, Unit Price, Total Amount. I need to help writing code whenerver chosing a value from combobox CustomerID on the main form values on subform will be changed accordingly.
I use access 2003 and SQL Server 2000 to accomplish this .adp file.
Tks for your kindly attention!

 

[raphael99]

Open the subform in design view.
Open the properties of the form
In the record source select the elipsees button on the right this will invoke the Build Wizard and it asks you to Do you want to create a query based on the table. Click Yes
Then select all the fields.
Save the query with a name something like frmSubProducts
Then select the criteria of the field which will match crom the combo box list. Next use the build wizard icon to then select the form and combo box.
Save and close out of the query.
Close the form
Next Open the form which has the combo, select something and the subform should filter accordingly.

If you have issues then upload a sample of the database.

Please give an help.
Which criteria should i Use in the field? Should I have some code?