Getting confused on method: Best way to update query by using combo box filters

[ST4RCUTTER]

I've searched through many posts here and read many off forum as well. The more I read, the more I am getting confused as to which method would work best.

Setup:
I have a table for work orders called tbl_workorders. This table has a many to one relationship to the table of customers. The field customer_ID is a foreign key on the Work order table and a primary key on the customer table.

What I'm trying to do:
I want two combo boxes with several text fields below for each of the fields on the work order table. These text fields would automatically update when values are chosen from the two combo boxes above.

I want the user to select a customer name from a combo box. This would then limit a list of work order numbers in the second combo box to only those that apply for that customer name. The user then selects the work order number they want and all the text boxes will update to that record.

I have created a query that contains all the fields from the work order table and the single field that I need from the customer table (customer name). At first I tried having the first combo box "look up" all the fields on the table and only showed the single column I needed in the combo box but I ran into a field limitation message when i tried to add all the fields on the query or the table.

How would you build two dependant combo boxes and a series of text fields sourced from a single query?

 

[ajetrumpet]

What I'm trying to do:
I want two combo boxes with several text fields below for each of the fields on the work order table. These text fields would automatically update when values are chosen from the two combo boxes above.

The MS kb article for this can be found here.

I want the user to select a customer name from a combo box. This would then limit a list of work order numbers in the second combo box to only those that apply for that customer name. The user then selects the work order number they want and all the text boxes will update to that record.

The code for the cascade method for combo boxes is here. You will want to build your rowsource for the second combo box though, using the query builder button at the end of the rowsource property line.

How would you build two dependant combo boxes and a series of text fields sourced from a single query?

I hope I have just answered this...

 

[ST4RCUTTER]

Thanks for the quick reply ajetrumpet!

Unfortunately I do not believe your answer to the first problem will work. The reason is that the query has too many fields that need to be driven off the combo box. For example, my query has 34 fields but I can only select 20 columns maximum when using multi-column selections. This means that when I update the combo box there will be 14 fields that cannot be updated via this method. I need another method to drive this update.

I am attempting the second method now!

 

[ajetrumpet]

my query has 34 fields but I can only select 20 columns maximum when using multi-column selections. This means that when I update the combo box there will be 14 fields that cannot be updated via this method. I need another method to drive this update.

Care to post a file on this? I would kinda like to see the method you are using...

 

[ST4RCUTTER]

Take a look at this! The DB is still in development. My question is in regard to the second tab on the main form called, "View Open Projects". I have a subform in there housing a query that I am trying to filter based on combo boxes above.


Thanks for any help you can provide!

 

[ajetrumpet]

Star,

After a few attempts, here is what I think should ultimately work (take a look at the file). I believe you can search the RecordSetClone for the relevant fields being matched on both combo boxes, and then go to that record if that is true. To do that, I wrote this code on the WorkOrder combo box AfterUpdate event:

Private Sub WorkOrder_AfterUpdate()

Dim rs As Recordset
Set rs = Me.RecordsetClone

  With rs
    .MoveFirst
    
    Do Until .EOF
    
      If !WorkOrder = Forms!frmMain!frm_Subquery.Form!WorkOrder And _
         !CustomerName = Forms!frmMain!cboFilterquery Then
           GoTo Message
      End If
    
    .MoveNext

    Loop

  rs.Close
  End With

MsgBox "Record Not Found!  Sorry!"
Set rs = Nothing

Exit Sub

Message:
  rs.Close
    Set rs = Nothing
      MsgBox "Record Found"

End Sub

I put the message box in there because the docmd.gotorecord command keeps telling me that the form is not open. It might be because I'm tired, but do I have the wrong form listed? I'm wondering...

I also tried filtering the recordset based on the two combo values, but the first filter sticks, and does not change after the first time. I don't use many filters, so maybe you can do something with it. I think there are more elegant ways to do this, but this post is meant to try and comply with your current setup.

The other methods that were not successful for me are located in the modules. I think your best bet is the recordset method that is with the form now, but hey, what do I know!? Anyway, check it out...