Error on INSERT INTO string 4

[GoinDeep]

I just yesterday got a lot of help on this, and learned a bunch, but I guess not enough. See Thread705-1391485

I am trying to add the last string, but obviously DLookUp is not the proper format, or I am doing it wrong:

DoCmd.RunSQL "INSERT INTO tbl_Activities(EntityID, ActivityDate, ActivityTime, ActivityCode, ServerID, ActivityNotes)" _
& " VALUES (" & Me!txtEntityID & "," _
& "#" & Format(Now, "yyyy-mm-dd") & "#," _
& "#" & Format(Now, "hh:nn") & "#," _
& "'SA'," _
& "'" & Me.txt_DataEntryInitials & "')," _
& DLookup("ServerName", "tbl_Servers", "ServerInitials ='" & [cboServerID] & "'")

 

[VRoscioli]

Unless there's more of your query below there, you need:

DoCmd.RunSQL "INSERT INTO tbl_Activities(EntityID, ActivityDate, ActivityTime, ActivityCode, ServerID, ActivityNotes)" _
        & " VALUES (" & Me!txtEntityID & "," _
        & "#" & Format(Now, "yyyy-mm-dd") & "#," _
        & "#" & Format(Now, "hh:nn") & "#," _
        & "'SA'," _
        & "'" & Me.txt_DataEntryInitials & "')," _
        & DLookup("ServerName", "tbl_Servers", "ServerInitials ='" & [cboServerID] & "'") 
[b]& ");"[/b]

-V

 

[PHV]

& [!]"'" &[/!] DLookup("ServerName", "tbl_Servers", "ServerInitials ='" & [cboServerID] & "'") [!]& "'")[/!]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JoeAtWork]

I would advise doing your DLookup once and copy into a variable so it isn't called for every record you insert.

Dim strServerName As String

strServerName = DLookup("ServerName", "tbl_Servers", "ServerInitials ='" & [cboServerID] & "'") 

DoCmd.RunSQL "INSERT INTO tbl_Activities(EntityID, ActivityDate, ActivityTime, ActivityCode, ServerID, ActivityNotes)" _
        & " VALUES (" & Me!txtEntityID & "," _
        & "#" & Format(Now, "yyyy-mm-dd") & "#," _
        & "#" & Format(Now, "hh:nn") & "#," _
        & "'SA'," _
        & "'" & Me.txt_DataEntryInitials & "')," _
        & "'" & strServerName & "');"

 

[PHV]

OOps, sorry for the typo:

& [!]"'" &[/!] DLookup("ServerName", "tbl_Servers", "ServerInitials ='" & [cboServerID] & "'") [!]& "')"[/!]

 

[GoinDeep]

You guys are a lot of help. I am close. I am trying to run 2 different "INSERT INTO" commands in the after update event, and it just doesn't work that way apparently. I get an error "2001" that says "You cancelled the previous operation." Here's the code I am using:

Dim strServerName As String
strServerName = DLookup("ServerName", "tbl_Servers", "ServerInitials ='" & [cboServerID] & "'") & "')"

DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tbl_Activities(EntityID, ActivityDate, ActivityTime, ActivityCode, ServerID)" _
& " VALUES (" & Me!txtEntityID & "," _
& "#" & Format(Now, "yyyy-mm-dd") & "#," _
& "#" & Format(Now, "hh:nn") & "#," _
& "'WE'," _
& "'" & Me.txt_DataEntryInitials & "')"

DoCmd.RunSQL "INSERT INTO tbl_Activities(EntityID, ActivityDate, ActivityTime, ActivityCode, ServerID, ActivityNotes)" _
& " VALUES (" & Me!txtEntityID & "," _
& "#" & Format(Now, "yyyy-mm-dd") & "#," _
& "#" & Format(Now, "hh:nn") & "#," _
& "'SA'," _
& "'" & Me.txt_DataEntryInitials & "')," _
& "'" & strServerName & "');"
DoCmd.SetWarnings True

 

[VRoscioli]

You need to add a semicolon to the end of your first SQL string.


-V

 

[GoinDeep]

Okay, there was a problem with my dlookup string that I fixed. Now I am getting the error "Number of query values and destination fields are not the same" Here it is:

Dim strServerName As String
strServerName = DLookup("ServerName", "tbl_Servers", "ServerID ='" & [cboServerID] & "'") & "')"

DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tbl_Activities(EntityID, ActivityDate, ActivityTime, ActivityCode, ServerID)" _
& " VALUES (" & Me!txtEntityID & "," _
& "#" & Format(Now, "yyyy-mm-dd") & "#," _
& "#" & Format(Now, "hh:nn") & "#," _
& "'WE'," _
& "'" & Me.txt_DataEntryInitials & "')"

DoCmd.RunSQL "INSERT INTO tbl_Activities(EntityID, ActivityDate, ActivityTime, ActivityCode, ServerID, ActivityNotes)" _
& " VALUES (" & Me!txtEntityID & "," _
& "#" & Format(Now, "yyyy-mm-dd") & "#," _
& "#" & Format(Now, "hh:nn") & "#," _
& "'SA'," _
& "'" & Me.txt_DataEntryInitials & "')," _
& "'" & strServerName & "');"
DoCmd.SetWarnings True

 

[Melagan]

This may not help your code run but it is better codeing practice.

Instead of DoCmd.RunSQL, run your hard-coded SQL statements by using CurrentDb.Execute. This way, you do not have to DoCmd.SetWarnings = False, (do SQL stuff), DoCmd.SetWarnings = True.


~Melagan
______
"It's never too late to become what you might have been.

 

[PHV]

Replace this:
strServerName = DLookup("ServerName", "tbl_Servers", "ServerInitials ='" & [cboServerID] & "'") & "')"
With this:
strServerName = DLookup("ServerName", "tbl_Servers", "ServerInitials='" & [cboServerID] & "'")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Melagan]

Also, you should store your SQL statements in string variables before attempting to run them in code - this way you can look at the whole picture in the debugger while you're running your code to make sure that the syntax is correct. It is a lot easier to read SQL in free-form than it is with all the extra VB markup. Example:

Dim strSQL1 as String
Dim strSQL2 as String

  strSQL1 = "INSERT INTO tbl_Activities(EntityID, ActivityDate, ActivityTime, ActivityCode, ServerID)" _
        & " VALUES (" & Me!txtEntityID & "," _
        & "#" & Format(Now, "yyyy-mm-dd") & "#," _
        & "#" & Format(Now, "hh:nn") & "#," _
        & "'WE'," _
        & "'" & Me.txt_DataEntryInitials & "')"
  strSQL2 = "INSERT INTO tbl_Activities(EntityID, ActivityDate, ActivityTime, ActivityCode, ServerID, ActivityNotes)" _
        & " VALUES (" & Me!txtEntityID & "," _
        & "#" & Format(Now, "yyyy-mm-dd") & "#," _
        & "#" & Format(Now, "hh:nn") & "#," _
        & "'SA'," _
        & "'" & Me.txt_DataEntryInitials & "')," _
        & "'" & strServerName & "');"

  CurrentDb.Execute strSQL1
  CurrentDb.Execute strSQL2

Set breakpoints on the CurrentDb.Execute lines then hold your mouse cursor over each if the strSQL variables, or type ?strSQL in the immediate window, so you can see your SQL statement. I'll bet you find the bug there =)

~Melagan
______
"It's never too late to become what you might have been.

 

[GoinDeep]

I like that method. I am still getting the same error. I can't find where in this string I am going wrong. I narrowed it down to strSQL2, but I'm stumped...the error is telling me number of fields is not matching the string, so I can only assume I have a comma in the wrong place and I can't find out where...so close...

strServerName = DLookup("ServerName", "tbl_Servers", "ServerID='" & [cboServerID] & "'")

strSQL2 = "INSERT INTO tbl_Activities(EntityID, ActivityDate, ActivityTime, ActivityCode, ServerID, ActivityNotes)" _
& " VALUES (" & Me!txtEntityID & "," _
& "#" & Format(Now, "yyyy-mm-dd") & "#," _
& "#" & Format(Now, "hh:nn") & "#," _
& "'WE'," _
& "'" & Me.txt_DataEntryInitials & "')," _
& "'" & strServerName & "');"

 

[PHV]

What is displayed in the immediate window (Ctrl+G) if you add this statement in your code:
Debug.Print strSQL2

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Melagan]

And this?

strSQL2 = INSERT INTO tbl_Activities(EntityID, ActivityDate, ActivityTime, ActivityCode, ServerID, ActivityNotes)" _
        & " VALUES (" & Me!txtEntityID & "," _
        & "#" & Format(Now, "yyyy-mm-dd") & "#," _
        & "#" & Format(Now, "hh:nn") & "#," _
        & "'WE'," _
        & "'" & Me.txt_DataEntryInitials & "'," _
        & "'" & strServerName & "');"

I found an Extra closing parenthesis on this line:
& "'" & Me.txt_DataEntryInitials & "')," _


~Melagan
______
"It's never too late to become what you might have been.

 

[GoinDeep]

Bam...that was it Melagan. Thanks for all your help. It wouldn't even catch that in the immed window for some reason. I don't know. My head is spinning now.