Adding new Record

[Marko24]

This is the code I use to add a new reocrd. Now my database has number of different tables and in order to put two tables on one FORM I needed to create a Secelt Statement in the record source to show all fields in both tables : SELECT COARSE.*, GRAD.* FROM COARSE INNER JOIN GRAD ON [COARSE].[SMPLNM]=[GRAD].[SMPLNM];

Here I then added a button so that I can create a new reocrd (mind you these two tables arelinked with the same primary key and that is SMPLNM.

Dim dbsCOARSE, dbsGRAD As Database
Dim rstCOARSE, rstGRAD As Recordset
Dim sampleNUM As String
' Return Database variable pointing to current database
sampleNUM = InputBox("Enter the new sample number: &quot
Set dbsCOARSE = CurrentDb
MsgBox "Before setting recordsets", vbOKOnly

Set rstCOARSE = dbsCOARSE.OpenRecordset("COARSE", dbOpenDynaset)

MsgBox "Before ADDNEW", vbOKOnly
With rstCOARSE
.AddNew
![SMPLNM] = sampleNUM
.Update
End With
rstCOARSE.Close

Set rstGRAD = dbsGRAD.OpenRecordset("GRAD", dbOpenDynaset)
Set dbsGRAD = CurrentDb
With rstGRAD
.AddNew
![SMPLNM] = sampleNUM
.Update
End With
MsgBox "After ADDNEW", vbOKOnly

rstGRAD.Close

New_Customer = True


NOW I GET a new record in the COARS TABLE but not in the GRAD table and there is a message that pops up saying: Object variable or width block variable not set.

HOW DO I FIX THIS??

 

[TonyJollans]

Hi Marko24,

From a quick look at your code, you are trying to use dbsGRAD before you have set it, so change the order of these two lines ..

Set rstGRAD = dbsGRAD.OpenRecordset("GRAD", dbOpenDynaset)
Set dbsGRAD = CurrentDb

.. should be ..

Set dbsGRAD = CurrentDb
Set rstGRAD = dbsGRAD.OpenRecordset("GRAD", dbOpenDynaset)

Enjoy,
Tony

 

[Marko24]

I have done that... but the record is still not added to the GRAD TABLE