Synchronizing Dynamic [B]Row[B]Source and RecordSource

[st3ve]

I've been wanting to use the same form to query three similar tables. I have managed to achieve the Record Source part...
(see link http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=59283)

Now, I need to select records from the chosen source table.

Basically I need to dynamically change the table name referenced in the Row Source, in sync with the Record Source choice. Currently I have the following sql in my Row Source property:

SELECT DISTINCTROW NPD1.RferenceNO, NPD1.FullName FROM NPD1 WHERE (((NPD1.FullName) Not Like "Dummy*"));

and ... Me.RecordsetClone.FindFirst "[RferenceNO] = '" & Me![Combo2] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark, where Combo2 is my 'querying' combo box.

The other tables are NPD2 and NPD3, and contain the same fields but a different data set.

Any ideas how i can achieve this? (Cobmo box containing selection is called cboChoice.)

eg something like this?
SELECT DISTINCTROW [combobox selection from cboChoice].RferenceNO, combobox selection from cboChoice].FullName FROM combobox selection from cboChoice].WHERE [combobox selection from cboChoice].FullName) Not Like "Dummy*"));?

or maybe not?

Thanks for your time and ideas.

 

[llkhoutx]

Have a combo box with the table names, then on its AfterUpdate event

dim ssql as string
if not isNull(me!cbo) then
ssql="SELECT * FROM " & ME!CBO
me!recordsource=ssql
me.requery
end If

You can use your fields, separated by commas, instead of the "*" in the ssql.

 

[st3ve]

Loading up of Row Source property

Thanks llkhoutx for the helpful suggestion. I have used this idea to help me progress my problem, but am still having difficulties.

It is the Row Source property of Combo2 that I need to dynamically change , synchronized with the (source table) selection made in cboChoice combobox.

I have added your code to the On Click property of Combo2 so that the RowSource is loaded at that time and I can then select my record from which to populate the form (using recordsetclone, in the after-update property).

Private Sub Combo2_Click()
Dim ssql As String
If Not IsNull(Me!cboSourceChoice) Then
ssql = "SELECT DISTINCTROW RferenceNO, FullName FROM " & Me!cboSourceChoice & " WHERE (((Me!cboSourceChoice.FullName) Not Like ""Dummy*""))"
'Me!RowSource = ssql
[Forms].[NPD All Parts (SubEdit)].[Combo2].RowSource = ssql
'Me.RowSource = "SELECT RferenceNO from NPD"
End If
Me.Requery
End Sub

This seemed the logical way to achieve my goal, but Combo2 does not show any records, if I leave the Row Source property blank (not un-reasonably, I guess) - but I assumed that the code that I have added to the on-click event would 'load up' the Row Source.

Is there something else i need to do? I suspect my code shown up in red may be a bit dodgy.

 

[llkhoutx]

I don't think the following part of your sql is correct:

Like ""Dummy*""))"

It should be

Like " & "Dummy*" & "))"

There's a method of doing this with """ (triple quotes), but I can never recall it.