Change forms' RecordSource by Combo box selection

[Guest]

I'm sorry my last post was ambiguous.
My form contains 2 combo boxes, the first box (Combo822) lists Makes from the
Oem table. When the user makes a choice I would like the form RecordSource
to change to a specific query. At the same time, the second combo box
(Combo2) should have it's RowSource property updated to the correct query.
This is the code I've written to change the RecordSource of the form:
Private Sub Combo822_AfterUpdate()
'after user choses make, change the forms' RecordSource to appropriate
query.
If Me!Combo822.Value = "Honda" Then
Dim pjm3 As ADODB.Connection
Set pjm3 = CurrentProject.Connection
Dim myRecordSet As New ADODB.Recordset
myRecordSet.ActiveConnection = pjm3
'I put SQL statement in variable named pjm3SQL.
Dim pjm3SQL As String
pjm3SQL = "SELECT JOBS.ID, JOBS.MODEL, JOBS.Picture"
pjm3SQL = pjm3SQL + "FROM JOBS;"
Me.Recordset = pjm3SQL
End If
End Sub
I get no error message but the forms' RecordSource property does not update.
I have not programmed the second combo box because I'm unable to solve this
problem yet.
I would appreciate any and all help/suggestions.
Thank You,
Paul3rd

 

[Guest]

I haven't seen your last post, so maybe I'm missing something here, but why
would you want to try to change the record source of your form everytime your
user makes a selection in your combo box? What if there are 50 choices? Are
you planning on having 50 different SQL statements in your code? If what
you're really trying to do is limit the available records based on the
selection in the combo box, then you are going about this all wrong. You also
should not need to change the rowsource of the second combo box. You can just
filter it based on the first combo. This is called "cascading combo boxes".
You can find info in your Access help file or do a google search.

As far as your code goes;

First, it is only going to run if someone selects "Honda". If they select
something else nothing will happen.

Second, you are attempting to create a recordset, but you said you want to
change the recordsource. There is a difference between a recordset and a
recordsource.

 

[Douglas J. Steele]

Take a look at

pjm3SQL = "SELECT JOBS.ID, JOBS.MODEL, JOBS.Picture"
pjm3SQL = pjm3SQL + "FROM JOBS;"

That's going to generate a string

SELECT JOBS.ID, JOBS.MODEL, JOBS.PictureFROM JOBS;

You need to add a space between the two lines:

pjm3SQL = "SELECT JOBS.ID, JOBS.MODEL, JOBS.Picture "
pjm3SQL = pjm3SQL & "FROM JOBS;"

(Note, too, that I changed your concatenation character from + to &. & is
the preferred concatenation character to use with text.)

 

[Guest]

Thanks for the info, I did find out how to set up "Cascading Combo Boxes" and
now my form works perfectly. There is a good tutorial @
http://community.netscape.com/n/pfx...libraryMessages&webtag=ws-msdevapps&tid=23626
A much simpler solution to what I was attempting.
Paul3rd

 

[Guest]

Thanks for the info. I found a simpler solution by not changing the
RecordSource at all, but using "Cascading Combo Boxes". I modified the code I
found @
http://community.netscape.com/n/pfx...libraryMessages&webtag=ws-msdevapps&tid=23626
Now the form works perfectly.
Paul 3rd