Populate form with info from other form's table

[VaughnIT]

I'm trying to create a form with a combobox that will show a list of Projects and will automatically populate, Customer number,PO number, company name, address, contact. Those fields are all entered in a form name Orders and are stored in the Orders table. I've created a combobox in my new form call PackingSlip. This new form works off of a table name printjobs.Printjobs and Orders are linked by the OrderID field.
How can I setup a combobox to select ProjectNumber to be displayed in the combobox and populate the other fields based on the Projectnumber selected by the user.
So far I have a combobox with the following

Control source:
Row Source: SELECT [Order].[OrderId], [Order].[CitationProjectNumber], [Locations].[CompanyID], [Locations].[CompanyName], [Locations].[LocationName] FROM (Locations INNER JOIN [Order] ON [Locations].[LocationID]=[Order].[ShipingLocationID]) INNER JOIN PrintJobs ON [Order].[OrderId]=[PrintJobs].[OrderID];

Column count: 5
Column widths:0";1";0";0";0"
Bound column: 2
List widht: 1

In the after_update of the combobox I have the following code was generated.

Private Sub Combo7_AfterUpdate()
  ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[CitationProjectNumber] = " & Str(Me![Combo7])
    Me.Bookmark = rs.Bookmark
End Sub

When I run the form to see what values come back I get a type mismatch in the line below.
rs.FindFirst "[Order].[CitationProjectNumber] = " & Str(Me![Combo7])

Citationprojectnumber is a Text type.

This is my first attempt at something like this and could use all the help I can get. so please anyone with experience with this I'd appreciate some help with the setup of this code.
If my aproach is completely wrong ... then make suggestions I got this idea after reviewing some other posts in this Forum... but as a novice it wasn't all clear so I'm pretty sure to have missed something.

Hope to hear from someone soon

 

[PHV]

For the type mismatch error, replace this:
rs.FindFirst "[Order].[CitationProjectNumber] = " & Str(Me![Combo7])
By this:
rs.FindFirst "Order.CitationProjectNumber='" & Me!Combo7 & "'"



Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244