Transfer data

G

Guest

I don't get an error but it also does not update my table!!

This code should create a new record in table: tblQualityProvider then copy
ICNNo into the new record.

(The GetNewID comes from a public function - I pasted it below this code).

Private Sub cmdProv_Click()
Dim gappname As String
Dim lCriteria As String
Dim lICCNNO As String
Dim lRacfid As String

lICCNNO = Me!ICNNo

If MsgBox("Any Message") = vbYes Then
DoCmd.SetWarnings False

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

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

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

DoCmd.SetWarnings True
DoCmd.GoToRecord , , acNewRec

End If
Forms!frmQualityData.Refresh
Forms!frmQualityData.Visible = True

Exit_cmdProv_Click:
Exit Sub
End Sub
///////////

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

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

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

Douglas J. Steele

What's actually contained in ICriteria? Try putting a

Debug.Print lCriteria

statement before the DoCmd.RunSQL statement, then go to the Immediate Window
(Ctrl-G) and see what's printed there.

If you create a new query and paste that SQL into it, does it run?
 
G

Guest

If I am doing what you say correctly and I run this Sub Test it bombs at
lICCNNO = Me!ICNNo

Also, If I just add the "Debug.Print lCriteria" and run it the way it was,
without the Sub test, there is no error or anything just like before!!


Sub test()

'Private Sub cmdProv_Click()
Dim gappname As String
Dim lCriteria As String
Dim lICCNNO As String
Dim lRacfid As String

lICCNNO = Me!ICNNo

If MsgBox("Any Message") = vbYes Then
DoCmd.SetWarnings False

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

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

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


DoCmd.SetWarnings True
DoCmd.GoToRecord , , acNewRec

End If
Forms!frmQualityData.Refresh
Forms!frmQualityData.Visible = True

Exit_cmdProv_Click:
Exit Sub
End Sub
 
D

Douglas J. Steele

Was there anything in Me!CNNo when you ran test()? (BTW, I never suggested
creating another sub!)

Did you check the Immediate Window (using Ctrl-G) to see what was printed
for the SQL statement?
 
G

Guest

The procedures you suggest seem simple to you but I don't do that very often.

I hit Ctrl+G and the Immediate window appears.
I add "Debug.Print lCriteria" just before the "DoCmd.RunSQL lCriteria".
Then I click run and it asks for me to create a macro - that's why I created
another sub..

Leaving the "Debug.Print lCriteria" in the code then opening the form and
running the code - it still does the same thing "nothing" my message pops up
then it just closes with out any error message.

Could you please help me test the code?
 
D

Douglas J. Steele

Sorry: what do you mean when you say that you "click run"?

When you have the Debug.Print ICriteria in the code and you open the form
and the code runs, then go to the Immediate Window to see what was printed
there.
 
G

Guest

While trying to get the Immediate Window to show me what is going on I
noticed one of my references was worng.

Now, when I run the code (with Debug) it does not like this line:
lCriteria = lCriteria & "SELECT " & lID & " AS ID, "tblQualityData.ICNNo"

It says "Compile Error Syntax Error"
I don't see anything wrong with it????
 
B

Bob Quintal

I don't get an error but it also does not update my table!!

This code should create a new record in table: tblQualityProvider
then copy ICNNo into the new record.

(The GetNewID comes from a public function - I pasted it below
this code).

Private Sub cmdProv_Click()
Dim gappname As String
Dim lCriteria As String
Dim lICCNNO As String
Dim lRacfid As String

lICCNNO = Me!ICNNo

If MsgBox("Any Message") = vbYes Then
DoCmd.SetWarnings False

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

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

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

DoCmd.SetWarnings True
DoCmd.GoToRecord , , acNewRec

End If
Forms!frmQualityData.Refresh
Forms!frmQualityData.Visible = True

Exit_cmdProv_Click:
Exit Sub
End Sub
///////////

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

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

If rs.RecordCount > 0 Then
rs.MoveLast
GetNewID = rs.Fields(0) + 1
Else
GetNewID = 0
End If
End Function
/////////
Click to expand...

You are trying to SELECT a value as a field name.
"SELECT " & lID & " AS ID,

try this instead.
lCriteria = "INSERT INTO tblQualityProvider(ID, ICNNo)"
lCriteria = lCriteria & " VALUES (" & IID & "," & Me!ICNNo & ");"
DoCmd.RunSQL lCriteria
Docmd.gotorecord,,acLast ' provided you are sorted on ID.
 
B

Bob Quintal

You are trying to SELECT a value as a field name.
"SELECT " & lID & " AS ID,

try this instead.
lCriteria = "INSERT INTO tblQualityProvider(ID, ICNNo)"
lCriteria = lCriteria & " VALUES (" & IID & "," &
Me!ICNNo & ");"
DoCmd.RunSQL lCriteria
Click to expand...

OOPS, I forgot a me.Requery before the docmd....
Docmd.gotorecord,,acLast ' provided you are sorted on ID.
Click to expand...
 
D

Douglas J. Steele

You've got an extra double quote in there (after ID,). It should be

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

And I've noticed something that may be the problem. You're missing some
spaces in your SQL statement due to not putting blanks at the end or
beginning of lines.

lCriteria = "INSERT INTO tblQualityProvider(ID, ICNNo) "
lCriteria = lCriteria & "SELECT " & lID & " AS ID, tblQualityData.ICNNo "
lCriteria = lCriteria & "FROM tblQualityData "
lCriteria = lCriteria & "WHERE (((tblQualityData.ICNNo)=" & """" & lICCNNO
& """" & "));"

Now that's going to suffer from word-wrap: here it is using line
continuation characters instead (and removing unnecessary stuff like extra
parentheses and punctuation):

lCriteria = "INSERT INTO tblQualityProvider(ID, ICNNo) " & _
"SELECT " & lID & " AS ID, tblQualityData.ICNNo " & _
"FROM tblQualityData " & _
"WHERE tblQualityData.ICNNo=""" & lICCNNO & """"
 
D

Douglas J. Steele

Bob Quintal said:
You are trying to SELECT a value as a field name.
"SELECT " & lID & " AS ID,

try this instead.
lCriteria = "INSERT INTO tblQualityProvider(ID, ICNNo)"
lCriteria = lCriteria & " VALUES (" & IID & "," & Me!ICNNo & ");"
DoCmd.RunSQL lCriteria
Docmd.gotorecord,,acLast ' provided you are sorted on ID.
Click to expand...

Other than the problems with spaces I pointed out, the original SQL was
legitimate, Bob. He's picking ICNNo up from tblQualityData, so it's
legitimate to use SELECT.
 
B

Bob Quintal

Other than the problems with spaces I pointed out, the original
SQL was legitimate, Bob. He's picking ICNNo up from
tblQualityData, so it's legitimate to use SELECT.
Click to expand...

Doug,

While it may be legitimate sql, there is an issue here that I take
with the original poster's statement
"This code should create a new record in table: tblQualityProvider
then copy ICNNo into the new record."

If there are several records with the same ICNNo, he'll get that
many new records, not one, unless ID is unique.

Since he's already filtering for ICNNo that matches a field on his
form, it's simpler to use the values version of insert.

There is also the issue of the end if for
If MsgBox("Any Message") = vbYes Then
which supresses the entire SQL generation process if the No button
is selected.
 
R

rquintal

There is also the issue of the end if for
If MsgBox("Any Message") = vbYes Then
which supresses the entire SQL generation process if the No button
is selected.
Click to expand...

Oy, I was half asleep when I wrote that. The default for msgbox() is
vbokayonly, which will fail the =vbyes,
therefore the code will never execute.
 
G

Guest

The message box is not an issue I'll add that back in later.

With Doug's help this code is much cleaner and effective but it still will
not create a new record on tblQualityProvider:
Two messages come up 1. You are about to append 1 row Yes/No. When I click
Yes - message 2. Microsoft cannot append all the records... If I click
"YES-do it anyway" the current form goes blank and no-new record is created
on the tblQualityProvider..
If I click No - it shows a run-time error 2501 RunSQL was canceled.

This is the current code, I have also pasted the Public function to create a
new record below it.


Dim lCriteria As String
Dim lICCNNO As String

lICCNNO = Me!ICNNo

Dim lID As Long
lID = GetNewID("tblQualityProvider")
lCriteria = "INSERT INTO tblQualityProvider(ID, ICNNo) " & _
"SELECT " & lID & " AS ID, tblQualityData.ICNNo " & _
"FROM tblQualityData " & _
"WHERE tblQualityData.ICNNo=""" & lICCNNO & """"

Debug.Print lCriteria
DoCmd.RunSQL lCriteria
/////////////

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

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

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

Bob Quintal

The message box is not an issue I'll add that back in later.

With Doug's help this code is much cleaner and effective but it
still will not create a new record on tblQualityProvider:
Two messages come up 1. You are about to append 1 row Yes/No.
When I click Yes - message 2. Microsoft cannot append all the
records... If I click "YES-do it anyway" the current form goes
blank and no-new record is created on the tblQualityProvider..
If I click No - it shows a run-time error 2501 RunSQL was
canceled.

This is the current code, I have also pasted the Public function
to create a new record below it.


Dim lCriteria As String
Dim lICCNNO As String

lICCNNO = Me!ICNNo

Dim lID As Long
lID = GetNewID("tblQualityProvider")
lCriteria = "INSERT INTO tblQualityProvider(ID, ICNNo) " & _
"SELECT " & lID & " AS ID, tblQualityData.ICNNo " & _
"FROM tblQualityData " & _
"WHERE tblQualityData.ICNNo=""" & lICCNNO & """"

Debug.Print lCriteria
DoCmd.RunSQL lCriteria
/////////////

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

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

If rs.RecordCount > 0 Then
rs.MoveLast
GetNewID = rs.Fields(0) + 1
Else
GetNewID = 0
End If
End Function
Click to expand...
Issue #1: db.OpenRecordset(tblName) does not guarantee that your
table will be sorted correctly, even if indexed. You may want to try
replacing tblQualityProvider with a query that does "SELECT * from
tblQualityProvider ORDER BY ID"

Issue #2: Your SELECT in the lCriteria declaration is not necessary
in this instance, since you already know the two values you wish to
insert. Consider using the VALUES () form of INSERT INTO, as it will
execute faster.

Bob Q
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Send data to table 11
Save Deleted Record 16
Capture Date deleted 5
OpenRecordSet Error 3
New ID 1
record changed values 1
Write to table then read data 1
User Defining Problem 2

Top