Send data to table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to add some data to a table with this command button code.
It hangs at: lID = GetNewID("tblLetterVals")

Says: Sub Function not defined

Could someone please help me define "GetNewID"



Private Sub ThankYou_Click()

Dim lCriteria As String
Dim lICCNNO As String

lICCNNO = Me!ICNNo

Dim lID As Long
lID = GetNewID("tblLetterVals")
lCriteria = "INSERT INTO tblLetterVals (ID, ICNNo, ProvNo)"

lCriteria = lCriteria & "SELECT " & lID & " AS ID,
tblQualityData.ICNNo, tblQualityData.ProvNo "

lCriteria = lCriteria & "FROM tblQualityData "
lCriteria = lCriteria & "WHERE (((tblQualityData.ICNNo)=" & """"
& lICCNNO & """" & "));"
DoCmd.RunSQL lCriteria
'Debug.Print lCriteria


DoCmd.SetWarnings True
DoCmd.GoToRecord , , acNewRec
 
It looks like function GetNewID is generating a key for table tblLetterVals,
you need to go into design view and see the definition of column 'ID', does
it say 'indexed unique'.
If so, in getNewID use the DMAX function (look in Help) to find the current
max value of ID and then add 1. This design will not be reliable if this is a
multi-user database.

-Dorian
 
Yes, this is a multi user database - and you say it will not be reliable??
What do you suggest ??? It took me forever to get this far!

I need to send data to a table so I can keep the data in a table and use it
for forms and reports..
 
It will be reliable if you change the DoCmd.RunSQL to
CurrentDb.Execute lCriteria, DAO.dbFailOnError
You can then trap the error if two users enter data at the same time (same
ID)

Pieter
 
'GetNewID("tblLetterVals")
lID = Nz(DMax("ID","tblLetterVals"),0) +1

Should do it

Pieter
 
I'm not sure what you are suggesting. I have tried to plug your code in
everywhere it seems logical but with no success. Where are you suggesting
plugging it into?

Private Sub ThankYou_Click()

Dim lCriteria As String
Dim lICCNNO As String

lICCNNO = Me!ICNNo

Dim lID As Long
lID = GetNewID("tblLetterVals")
lCriteria = "INSERT INTO tblLetterVals (ID, ICNNo, ProvNo)"

lCriteria = lCriteria & "SELECT " & lID & " AS ID,
tblQualityData.ICNNo, tblQualityData.ProvNo "

lCriteria = lCriteria & "FROM tblQualityData "
lCriteria = lCriteria & "WHERE (((tblQualityData.ICNNo)=" & """"
& lICCNNO & """" & "));"
CurrentDb.Execute lCriteria, DAO.dbFailOnError
'DoCmd.RunSQL lCriteria
'Debug.Print lCriteria


DoCmd.SetWarnings True
DoCmd.GoToRecord , , acNewRec
 
Pieter is suggesting that you replace the line of code

lID = GetNewID("tblLetterVals")

with the line of code

lID = Nz(DMax("ID","tblLetterVals"),0) +1
 
I get this error- is it a references error???

Run-Time error '2471':
The expression you entered as a query parameter produced this error:
'The object dosen't contain the Automation object 'ID."
 
when, where?
I've just used your variables & field names
check for any typos in my or your code

Pieter
 
Back
Top