filter subform from main form combobox

[rblair11]

I have a main form with an unbound combo box. I also have a subform showing records in datasheet view.

on the after_update event of the combobox I want to filter the subform. Here is my code (note frmqrysubmittals is the subform and the recordsource for the subform is initially the RS1 portion of code):

Dim RS1 As String
Dim RS2 As String
Dim RS3 As String

RS1 = "SELECT tblSubmittals.SubmittalID, tblSubmittals.Format, tblSubmittals.[Specification Section], tblContractorInfo.Contractor, tblSubmittals.[Project Number], tblSubmittals.Description FROM tblContractorInfo INNER JOIN tblSubmittals ON tblContractorInfo.ContractorID = tblSubmittals.ContractorID"
RS2 = " WHERE (((tblContractorInfo.Contractor)=" & Chr$(34) & Me.cboContractor.Value & Chr$(34) & "));"
rs3 = RS1 & RS2


Me.frmqrySubmittals.Form.RecordSource = rs3
me.frmqrySubmittals.form.requery

When I update the cbo box the subform doesn't display any records. When I go to design view and look at the record source it displays the RS1 portion (the original RS), but all of the terms are placed in brackets [].

Note, the subform and contorl are both named frmqrySubmittals.

I've been at this for days. What am I doing wrong?

Thank you everyone for your help. This group has been a great resource over the past few months as I stuble through learning Access.

 

[WayneRyan]

r,

I don't think it can be Me.cboContractor.value

Dim RS1 As String

RS1 = "SELECT tblSubmittals.SubmittalID, " & _
      "       tblSubmittals.Format, " & _
      "       tblSubmittals.[Specification Section], " & _
      "       tblContractorInfo.Contractor, " & _
      "       tblSubmittals.[Project Number], " & _
      "       tblSubmittals.Description " & _
      "FROM tblContractorInfo INNER JOIN tblSubmittals ON " & _
      "        tblContractorInfo.ContractorID = tblSubmittals.ContractorID "
      "WHERE (((tblContractorInfo.Contractor) = " & Chr$(34) & Me.cboContractor.Value & Chr$(34) & "));"
                                                                        ^
                                                                        |
         Me.cboContractor --> Forms!YourMainForm!cboContractor   -------+

Me.frmqrySubmittals.Form.RecordSource = rs1
me.frmqrySubmittals.form.requery

Wayne

 

[rblair11]

I tried and it still isn't working. However, I looked at the properties window for the subform after the code runs and the record source IS correct. I verified this by coping it, opening up the form directly, and pasting it into the record source.

I'm missing something. Here is the code:

Private Sub cboContractor_AfterUpdate()

Dim RS1 As String

RS1 = "SELECT tblSubmittals.SubmittalID, " & _
" tblSubmittals.Format, " & _
" tblSubmittals.[Specification Section], " & _
"tblContractorInfo.Contractor, " & _
"tblSubmittals.[Project Number], " & _
"tblSubmittals.Description " & _
"FROM tblContractorInfo INNER JOIN tblSubmittals ON " & _
"tblContractorInfo.ContractorID = tblSubmittals.ContractorID " & _
"WHERE (((tblContractorInfo.Contractor)=" & Chr$(34) & Forms!frmSubmittalEntry.cboContractor.Value & Chr$(34) & "));"

Me.frmqrySubmittals.Form.RecordSource = RS1
Me.frmqrySubmittals.Form.Requery
MsgBox (RS1)

If you are willing to look at the DB itself I can send it over. It just has dummy data in it right now anyway.

 

[WayneRyan]

RB,

Forms!frmSubmittalEntry.cboContractor.Value

I just re-read this ... I thought you were on the subform.

Is there a chance that your combo is multicolumn?

How about --> Me.cboContractor.Column(1)

The subscripts start at 0, the 0 is probably the ID.

Wayne

 

[rblair11]

Thanks for the suggestion

It is not a multi column combo. I tried the code just in case but not luck. I also change it back to me.cboContractor.value and found that the same thing happens as with Forms!frmSubmittalEntry.cboContractor.Value.

I've found something strange. As I said, after the code runs the Record Set in the subform is correct but doesn't show any records. I close the mainform and open the form that the subform is based on (frmqrySubmittals) and its record set has not been updated.

Here's where it get's strange. If I run the code and look at the record set of the submform in the properties page as above but now I right click and select "zoom" to view the record set it is correct. Then I close the zoom window and try to exit the main form. When I do that it asks me if want to save the changes and I say yes. Now when I open the form that the subform is based on (frmqrySubmittals) its recordset IS updated by the value selected in the cbobox. Then I open the original form again and the record set in the subform is still correct, matches the record set in the form it is base on, and shows the proper records. Then if I choose another value in the cbobox the whole loop starts again (subform doesn't show any records, right click zoom, save, close, open).

Somthing else I noticed. If I choose a value in the cbobox the subform goes blank (doesn't show any records). Then if I go into design mode and then back to form view the subform shows the records based on the record set before the cboselection.

I apologize if this is confusing. Bottom line, the code works, sort of. For some reason the form that the subform is based on doesn't get updated unless I am prompted "do you want to save changes to the design of frmqrysubmittals?" and then select yes. Note frmqrysubmittals is the form that the subform is based on.

 

[WayneRyan]

R,

Can you go ahead and post the DB here?

Wayne

 

[rblair11]

Here it is. It's still very rough at this point.

Thanks!