Send data to table

[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

 

[Guest]

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

 

[Guest]

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..

 

[Pieter Wijnen]

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

 

[Guest]

I added your suggestion, but there is no change in the error:
Any suggestions?

 

[Pieter Wijnen]

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

Should do it

Pieter

 

[Guest]

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

 

[Douglas J. Steele]

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

 

[Guest]

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."

 

[Pieter Wijnen]

when, where?
I've just used your variables & field names
check for any typos in my or your code

Pieter

 

[Guest]

I did not add a field for the new ID in my SQL table..

Thank you very much for your help....

 

[Pieter Wijnen]

Yes, that sometimes help <g>

Pieter

I did not add a field for the new ID in my SQL table..

Thank you very much for your help....