User Defining Problem

[Dan @BCBS]

I'm trying to copy data to a new table.

My command botton code grabs the data, creates a new record in the
destination table and dumps it in. It reads a public function called
"GetNewID" to create a new record.

I'm having a problem defining a place for the data.
Here is both the public function and the command button code:


Public Function GetNewID(tblName As String) As Long
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset(tblName)
' Set rs = HoldData

If rs.RecordCount > 0 Then
rs.MoveLast
GetNewID = rs.Fields(0) + 1
Else
GetNewID = 0
/////////////

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

 

[Tom van Stiphout]

On Fri, 5 Oct 2007 14:01:49 -0700, Dan @BCBS

You're using confusing language, at least to me.

GetNewID does not create a new record. It merely returns the next ID
value to use.

"a place for the data"? According to your Insert statement that would
be tblLetterVals.

What exactly is the problem? Are you getting any error messages?

-Tom.

 

[Dan @BCBS]

Yes
Run-Time error - 13 Type mismatch
At this point: Set rs = db.OpenRecordset(tblName)

When I run this I am expecting to copy the values to tblLetterVals.

Any ideas?