Displaying data in a subform from a combo box in the main form

[leahb747]

Okay so,

I have a main form that has a combo box in which you select the company and division (one combo box, two columns) for which you want to enter data. The record source for the main form is the query that gives the two columns.

I then have a subform beneath and what I want to happen is that when the company is selected in the combo box, the subform automatically displays the company ID and division ID for this company/division and then the rest of the information can be typed in. I've tried a few different things and this is what I have so far.

SELECT [Bids and Proposals Register].[Company ID] FROM Bids and Proposals Register LEFT JOIN Company Query ON [Bids and Proposals Register].[Company ID] = [Company Query].[Company ID] WHERE (([Company Query].[Company Name]) = [Forms]![Bids and Proposals Register]![cboCompanyName]);
The subform is Bids and Proposal Register, and the Company Query is the Record Source of the main form. cboCompany Name is the drop down with the Company name and Company ID in it. I'm getting an error message that says "Syntax error in FROM clause"

Any help would be greatly appreciated

 

[Bryan]

I have a main form that has a combo box in which you select the company and division (one combo box, two columns) for which you want to enter data. The record source for the main form is the query that gives the two columns.

The main form should be unbound. Create the combo by going through the wizard and insure that the PK is one of the columns selected.

I then have a subform beneath and what I want to happen is that when the company is selected in the combo box, the subform automatically displays the company ID and division ID for this company/division and then the rest of the information can be typed in. I've tried a few different things and this is what I have so far.

I don't think you need the where clause in your query. The link between what is selected in the combo box and what is displayed in the subform is done by specifying fields in the Master/Child properties of the subform.

Take a look at THIS to see if it helps.

 

[leahb747]

Thanks for that. I've got it under control now. I sort of changed it up a bit and I now have a text box with a combo box/button next to it. The combo box/button selects the company ID, division ID, company name and division. But this has created another drama. There are 3 options to select from in the box, but the selection is stuck on just one. You can open the drop down and click but nothing but the first selects? Any ideas??

 

[leahb747]

Also, this is the AfterUpdate code I'm running

Private Sub cboResourceFind_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.RecordsetClone
rs.FindFirst "[Company ID] = '" & Me![cboResourceFind] & "'"
Me.Bookmark = rs.Bookmark
End Sub

cboResourceFind is the combo box I'm selecting the 4 pieces of data from, and Company ID is the name of the control it needs to fill. I've taken this code from a database that works really well and checked through all of the properties and I keep getting an error that says it needs to Debug. It's highlighting the FindFirst part

 

[Bryan]

Also, this is the AfterUpdate code I'm running

Did you manually enter that code based upon the example in the other database? I'm not 100% sure, but I think that's the code that Access will generate if you use the wizard. At any rate, I've seen it before.

Try generating the combo box using the wizard and see what AfterUpdate code it gives you. That might fix the syntax problem as well.

 

[Bryan]

The main form should be unbound.

I may have given you some bad advice here. Try binding the main form to the table (Bids and Proposal Register?).

Also, you may want to consider NOT using spaces in object names in the future. They're just one more thing to cause issues down the road.

 

[vbaInet]

You're trying to set the bookmark of the subform not the main form so your code should look like this:

Private Sub cboResourceFind_AfterUpdate()
' Find the record that matches the control.
    Dim rs As DAO.Recordset
    
    Set rs = Me.[COLOR=red]SubformControlName[/COLOR].RecordsetClone
    
    rs.FindFirst "[Company ID] = '" & Me.cboResourceFind.Value & "'"
    
    Me.[COLOR=red]SubformControlName[/COLOR].Bookmark = rs.Bookmark
    
    Set rs = Nothing
End Sub

Amend the red part.

 

[leahb747]

Thanks guys. It's working now but it was due to the fact it was based on company ID not Division ID. In the database, companies can be listed more than once because they have divisions that are specific to them. All good!!

 

[vbaInet]

Good to hear!