RowSource switch using toggle button

[megamef]

Hi All,

I have a form that contains a combo box, I used the access wizard to make this combo box select a record from a table of customers and display that record on the form.

The user then has the option to print that record.

This works fine.

I now want to be able use the same combo box but with a different table so I can select Records from a list of suppliers.

Is there a way I could use a toggle button to change the rowsource of the combo box and then use that same combo box to select records from my supplier table.

cheers

Steve

 

[megamef]

Tottaly forgot to mention but i've had a go at this by using this in the toggle button:

Private Sub Toggle13_Click()
If Me.Toggle13 Then
Me.Combo1.RowSource = "SELECT T_Supplier_Dropdownlist.supplier FROM T_Supplier_Dropdownlist;"
Else
Me.Combo1.RowSource = "SELECT T_Customers.CustomerNumber, T_Customers.CustomerName FROM T_Customers;"
End If
End Sub

and this in the afterupdate:

If Me.Toggle13 Then
    Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[supplier] = " & Str(Nz(Me![Combo1], 0))
    If Not rs.EOF Then Me.Bookmark = rs.BookmarkElse
Else
    Dim rs2 As Object
    Set rs2 = Me.Recordset.Clone
    rs2.FindFirst "[CustomerNumber] = " & Str(Nz(Me![Combo1], 0))
    If Not rs2.EOF Then Me.Bookmark = rs2.Bookmark
End If

but i'm doing something wrong somewhere as i get the error:
type mismatch

 

[megamef]

I'm wasting everyone's time here, i just made another form and put the supplier list on there and just used a button to link to that. Sorry.