Inserting data and retrieving fields

[sovereign]

Afternoon,



So after lots of very helpful advice form this forum!

i have the below code which inserts data and then i want to retrieve the value in the first two columns but firstly it says "you must use dbseechanges" but i have put this into the code you may see below,

 Dim db As DAO.Database
Dim rs As DAO.Recordset
dim sSQL as string 


sSQL = "INSERT INTO claims (Clientcc, DriverCc, Crefname, Crefcc, creg, cadt) VALUES ('" & ClientCc & "', '" & DriverCc & "', '" & CrefName & "', '" & CrefCc & "', '" & Creg & "', '" & DOA & "')"


Set db = CurrentDb


db.Execute sSQL


Set rs = db.OpenRecordset("claims", , dbSeeChanges)


CClc = rs.Fields(0)
Debug.Print rs.Fields(0)


CClaimref = rs.Fields(1)
Debug.Print CClaimref




rs.Close
Set rs = Nothing


db.Close
Set db = Nothing

Once again big thanks to anyone that helps!

[pbaldy]

Try

Set rs = db.OpenRecordset("claims", dbOpenDynaset, dbSeeChanges)

[sovereign]

Try

Set rs = db.OpenRecordset("claims", dbOpenDynaset, dbSeeChanges)

Ahh that solved that problem!

you are a star!!!

How do i set it to return the values of the data i just entered and not the 'first' record?

Really appreciate all your help.

[pbaldy]

If you're trying to add a record and then return values from it (one would ask why, since you already have the values), I'd do it all with a recordset. Here's an example and then getting the autonumber from the record just added:

        With rsMaster
          .AddNew
          !Field1 = 123
          !Field2 = "abc"
          .Update
          .Bookmark = .LastModified
          lngARKey = !AR_Key
        End With

[sovereign]

If you're trying to add a record and then return values from it (one would ask why, since you already have the values), I'd do it all with a recordset. Here's an example and then getting the autonumber from the record just added:

        With rsMaster
          .AddNew
          !Field1 = 123
          !Field2 = "abc"
          .Update
          .Bookmark = .LastModified
          lngARKey = !AR_Key
        End With

Thanks again.

The fields i want to return one is the primary key and the other is a number generated by a function in the sql server. so they arent values i already have.

[sovereign]

got it working so once again massive thanks for your help!

im having an issue i haven't experienced before when the new record in the sub-form is created i cant leave that record, i can move the courser etc but cannot click any where.

Regards

[pbaldy]

Happy to help!

[sovereign]

Happy to help!

Once again i am struggling, for some reason it will not pull the latest record seems that it is pulling a random record (but does increment the record it pulls one up each time)

Dim db As DAO.DatabaseDim rs As DAO.Recordset




Set db = CurrentDb


'db.Execute sSQL


Set rs = db.OpenRecordset("claims", dbOpenDynaset, dbSeeChanges)


With rs
rs.AddNew
rs!ClientCc = CClientCc
rs!DriverCc = CDriverCc
rs!CrefName = CCrefCc
rs!Creg = CCreg
rs!Cadt = CDOA
rs.Update
rs.Move 0, rs.LastModified
'get the values to return


CClc = rs!CLc
CClaimref = rs!ClaimRef
rs.Close
db.Close
        




Debug.Print CClc




Debug.Print CClaimref


End With


Set rs = Nothing


Set db = Nothing

So in short it is pulling data but not from the record i have just added.

[pbaldy]

You didn't use the method I posted to get to that record for some reason.

.Bookmark = .LastModified

[sovereign]

You didn't use the method I posted to get to that record for some reason.

.Bookmark = .LastModified

I used that to start of with and it returns the same values. Was testing a few options.

Could this be related to the save issue i am having above?

Maybe i will rebuild the subform and table and see if there is just somthing there that is causing the issues

[pbaldy]

I don't think so. The .Update saves the new record, the .Bookmark line should go to the just-saved record. Can you post a sample db where it doesn't?