Modify data in 2 tables using 2 recordsets

[Dynein]

Hello everyone!
You have been a great help so far, so I hope you can help me with my newest problem to.
I am using the following code:

Private Sub Savebutton_Click()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("Table1", dbOpenDynaset)


'Do stuff with rst
rst.close

Set rst2 = db.OpenRecordset("Table2", dbOpenDynaset)
'Filter for combobox ID
rst2.Filter = "ID = '" & Me.F1.Column(4) & "'"
rst2F1 = rst2.OpenRecordset

'Do stuff with rst2

Set db = Nothing
Set rst2 = Nothing
Set rst = Nothing


End Sub

Now I am always getting the error message about a datatype conflict highlighting the rst2F1 = rst2.OpenRecordset part

I am confused about this ... Why am I not abled to use the filtered 2nd recordset?

Thank you for your help!

 

[sneuberg]

What are you trying to do with

rst2F1 = rst2.OpenRecordset

I don't see rstF1 declared anywhere. What is it suppose to be?

Edit: Maybe you should declare it as a DAO.Recordset as shown in this example of using a filter.

 

[Dynein]

What are you trying to do with

rst2F1 = rst2.OpenRecordset

I don't see rstF1 declared anywhere. What is it suppose to be?

I am trying to define it as the Filtered recordset. So the active recordset will only contain the records I want to edit.

next thing would be something like

rst2F1.edit
rst2F1!field = value



I used a similar code in other forms like

rst.Filter = ...(conditions here)
rstFiltered = rst.OpenRecordset

 

[Minty]

You would be better off opening the rescordset with the filter applied, that way you are only bringing in the records you want. e.g.

Set rst2 = db.OpenRecordset("SELECT * FROM Table2 WHERE ID = '" & Me.F1.Column(4) & "'", dbOpenDynaset)

Edit: There is a good discussion about multiple recordsets here https://access-programmers.co.uk/forums/showthread.php?t=219601

 

[Dynein]

What are you trying to do with

rst2F1 = rst2.OpenRecordset

I don't see rstF1 declared anywhere. What is it suppose to be?

Edit: Maybe you should declare it as a DAO.Recordset as shown in

declaring it as a DAO.Recordset didnt help

 

[Dynein]

You would be better off opening the rescordset with the filter applied, that way you are only bringing in the records you want. e.g.

Set rst2 = db.OpenRecordset("SELECT * FROM Table2 WHERE ID = '" & Me.F1.Column(4) & "'", dbOpenDynaset)

This is resulting in another error message
"the select statement includes a reserved word or an argument name that is misspelled or missing"

so i am getting the feeling that the whole problem is resulting from the combo box.

I am using a combobox in the form, where you can select an existing record of the database, and it will show you the name of the record. Yet the names are not exclusive, so if I want to make changes in the record I need to know the exact ID. Which is saved in the 5 column of the combobox.
If I am using MsgBox (Me.F1.column (4)) I am getting the right ID ...

Edit: Ok I just forgot to put in the *
After adding it to the code, I am getting the same error message as before

 

[Minty]

This is where building the sql outside of the rs command can help - try adding this;

Dim sSql as string

sSql = "SELECT * FROM Table2 WHERE ID = [COLOR="Red"]'[/COLOR]" & Me.F1.Column(4) & "[COLOR="red"]'[/COLOR];"

Debug.print sSql

Set rst2 = db.OpenRecordset(sSql, dbOpenDynaset)

When you open the debug window open the immediate window (Ctrl+G) and you will see what the sSql Query string actually is.

Edit - DOH - and your problem is the quotes around the ID assuming it a number field. Remove them!

 

[Dynein]

Edit - DOH - and your problem is the quotes around the ID assuming it a number field. Remove them!

Oh boy now I am feeling stupid ...
This solved the problem, thank you!