Get Data from two tables

[wiremonkey22]

I have two linked (text) tables in which i pull records into two other tables only to delete the spaces now i want to combine these two tables with a query the problem is they do not have anything in common ive tried to use an autonumber but these tables never have the same number of records
what i want is the first column in table1 and columns 1-5 in table two to align in the new table however when i run the query with both tables i only get the first record in table1 and all the records from table2 is it possible in VBA or a Query or any eaiser way to take record 1 from table1 and record 1 from table2 to and combine in new table for all the records.

Ive played with the following code but unsuccessful

Public Function UpdateProgrammer()
Dim db As Database
Dim Numbrec As DAO.Recordset 'table 1'
Dim Lenrec As DAO.Recordset 'table 2'
Dim Programer As Recordset 'new table'
Set Programer = db.OpenRecordset("tblNumber_LenMulti")
Set db = CurrentDb
Set Numbrec = db.OpenRecordset("tblNumbers", dbOpenDynaset)
Set Lenrec = db.OpenRecordset("tblLens", dbOpenDynaset)
Lenrec.MoveFirst
Do While Not Lenrec.EOF
With Programer
.AddNew
!Number = Numbrec!Numbers
!Group = Lenrec!Group
!Len1 = Lenrec!Len1
!Len2 = Lenrec!Len2
!Len3 = Lenrec!Len3
!Len4 = Lenrec!Len4
End With
Lenrec.MoveNext
Loop
End Function

Get object variable or with block not set error
Not much experience with the With function or loops sorry if this is newbie error
Thanks in advance

 

[pbaldy]

For starters, you use the db variable before actually setting it.

 

[wiremonkey22]

Do you mean it needs to go

Public Function UpdateProgrammer()
Dim db As Database
Dim Numbrec As DAO.Recordset 'table 1'
Dim Lenrec As DAO.Recordset 'table 2'
Dim Programer As Recordset 'new table'
'HERE' Set db = CurrentDb
Set Programer = db.OpenRecordset("tblNumber_LenMulti")
Set db = CurrentDb 'NOT HERE'
Set Numbrec = db.OpenRecordset("tblNumbers", dbOpenDynaset)
Set Lenrec = db.OpenRecordset("tblLens", dbOpenDynaset)

 

[pbaldy]

Yes; you use it in the recordset line, and it had not been set yet, thus the "variable not set" error.

 

[wiremonkey22]

Ok that works now but some thing is wrong with this With statement because now it says Update or Cancel Update without Addnew or edit but i have the
.AddNew is it in the wrong place or missing something

Public Function UpdateProgrammer()
Dim db As Database
Dim Numbrec As DAO.Recordset 'table 1'
Dim Lenrec As DAO.Recordset 'table 2'
Dim Programer As Recordset 'new table'
Set db = CurrentDb
Set Programer = db.OpenRecordset("tblNumber_LenMulti")
Set Numbrec = db.OpenRecordset("tblNumbers", dbOpenDynaset)
Set Lenrec = db.OpenRecordset("tblLens", dbOpenDynaset)
Lenrec.MoveFirst
Do While Not Lenrec.EOF
With Lenrec
.AddNew
'New table from Table2'
Programer!Group = Lenrec!Switch
Programer!Len1 = Lenrec!Len1
Programer!Len2 = Lenrec!Len2
Programer!Len3 = Lenrec!Len3
Programer!Len4 = Lenrec!Len4
End With
Lenrec.MoveNext
Loop
End Function

 

[wiremonkey22]

Got it to work thank you for your help this is the final code

Public Function UpdateProgrammer()
Dim db As Database
Dim Numbrec As Recordset 'table 1'
Dim Lenrec As Recordset 'table 2'
Dim Programer As Recordset 'new table'
Set db = CurrentDb
Set Programer = db.OpenRecordset("tblNumber_LenMulti")
Set Numbrec = db.OpenRecordset("tblNumbers")
Set Lenrec = db.OpenRecordset("tblLens")

'didnt move to the first of both tables'

Numbrec.MoveFirst
Lenrec.MoveFirst
Do While Not Lenrec.EOF
With Programer
.AddNew
!Number = Numbrec!Numbers
!Group = Lenrec!Switch
!Len1 = Lenrec!Len1
!Len2 = Lenrec!Len2
!Len3 = Lenrec!Len3
!Len4 = Lenrec!Len4

'forgot the update'

.Update
End With
Numbrec.MoveNext
Lenrec.MoveNext

Loop

End Function