Need form link to SQL help

[dan231]

I need to create a new form in order to add new City, St and Zip to a pre-existing ZipCode table. The front end is Access and the backend is SQL.

I was looking at other form and there is code that shows it is adding the new data directly into the sql table.

Actual code:

Private Sub btnAdd_Click()
    Dim strAddSQL As String
    Dim strNewCode As String
    Dim datCurrentDate As Date
    Dim datCurrentTime As Date
    
    strNewCode = CheckQuotes(txtNewCode)
    
    strPrompt = "Do you want to add the following code " & vbCrLf & _
        Chr(9) & "'" & strNewCode & "'"
    intUserResponse = MsgBox(strPrompt, vbYesNo)
    
    If intUserResponse = vbNo Then
        Exit Sub
    End If
    
    datCurrentDate = DATE
    datCurrentDate = Time()
    
    strAddSQL = "INSERT INTO tblMunicipalityCode " & _
        " (MunicipalityCodeDesc, AddDate, AddTime, AddUserID) " & _
        " VALUES('" & strNewCode & "', #" & datCurrentDate & "#, #" & datCurrentTime & "#, " & intEmployeeID & ")"
    
'    MsgBox strAddSQL
    
    DoCmd.SetWarnings False
    DoCmd.RunSQL (strAddSQL)
    DoCmd.SetWarnings True
    
    lbxAvailableCodes.Requery
    
End Sub

Do I have to include this type of code for my Zip Code form?
The Zip table only has City, St, Zip fields, so I would think it can't be too difficult to figure this out.
If so, I think I can figure it out, but can someone tell me what the # means?

 

[pbaldy]

In Access, Date/time values must be surrounded by #, text values by the single quote, numeric values nothing. You have all three types demonstrated there.

 

[dan231]

Great! Thank you for that clarification.

 

[dan231]

I have checked other forms in use and they all seem to be adding directly to the SQL db. So I have taken the above code and modified it to fit my needs for the new data, form and table.

I do not have a test system in place; can someone check this over and see if there might be any noticeable errors? I have added 'my notes' to each part to see if I understand what is happening correctly. Any helpful comments on this would be greatly appreciated!

New code to add Zip Code data into Zip Code table.

   Private Sub btnAdd_Click()
[COLOR=Green]' these are defining string elements in the form. [/COLOR] 
      Dim strAddSQL As String   
      Dim strNewCity As String
      Dim strNewState As String
      Dim strNewZip As String
      

[COLOR=Green]' I think this is telling the system to find the data on the form and assign it to the string elements[/COLOR]

      strNewCity = CheckQuotes(txtNewCity)
      strNewState = CheckQuotes(txtNewState)
      strNewZip = CheckQuotes(txtNewZip)
  

[COLOR=Green]' This is adding a pop up message box asking if the following is ok to add.  I have no idea what the [ & vbCrLf & _   [/COLOR][COLOR=Green]Chr(9) & ] means[/COLOR]

      strPrompt = "Do you want to add the following data? " & vbCrLf & _
          Chr(9) & "'" & strNewCity & "'" & "'" & strNewState & "'" & "" & strNewCity & ""
      intUserResponse = MsgBox(strPrompt, vbYesNo)
[COLOR=Green]
' this is an if statement that if the user selects NO the window will close and if YES, the rest of the code will run [/COLOR]   
      If intUserResponse = vbNo Then
          Exit Sub
      End If
      
[COLOR=Green]' this is the actual add the data to the SQL table (tblZipCodes)  The table fields are defined as (Zip), (State) and (City) and the data to be added is found in the strings (strNewCity), (strNewState) and (strNewZip).  The new data for State and City are text so the single quote is used and the zip is all numbers so no single quote is used.[/COLOR]
      strAddSQL = "INSERT INTO tblZipCodes " & _
          " (Zip, State, City) " & _
          " VALUES('" & strNewCity & "', '" & strNewState & "', " strNewZip ")"
  
[COLOR=Green]' this is setting a warning to False (I don't know what this is)    [/COLOR]    
      DoCmd.SetWarnings False
[COLOR=Green]' is this is running the actual command to add the sql data?[/COLOR]
      DoCmd.RunSQL (strAddSQL)
[COLOR=Green]' this is setting the warning back to True (I don't know what this is) [/COLOR]
      DoCmd.SetWarnings True

[COLOR=Green]' is this is requering the table so the new data is now added and I won't have to reopen the form to find the new data?[/COLOR]
      lbxAvailableCodes.Requery
      
  End Sub

 

[pbaldy]

The SetWarnings lines are to avoid the standard system messages of "You are about to append x records". You turn the warnings off, run your query, then turn them back on so you still get other warnings that you may want. You can drop both of those lines if you switch to this, which is more efficient anyway:

CurrentDb.Execute strAddSQL

I would normally put zip codes in a text field, because they can have leading zeros and sometimes a dash in the middle.

 

[dan231]

Good thinking, I will change that zip code field.
Thank you again!

 

[dan231]

I implemented the code/form last night and it didn't work.
The first thing is that the debugger didn't like the char underscore:

strPrompt = "Do you want to add the following data?" &  vbCrLf & [COLOR=Red]_[/COLOR] Chr(9) & "'" & strNewCity & "'" &  "'" & strNewState & "'" &  "’" & strNewCity & "’"

the underscore was seen as invalid in the debugger, I removed it and saved the form. When I opened the form and tried to add a new zip code I did not get the pop up msg box nor did the table update.

I'm thinking I have the syntax incorrect for this part. I don't know if since this part didn't work the actual add to the SQL part isn't going to work either.

 

[dan231]

Re: Need form link to SQL help (SOLVED)

I redid the code and I must have had a bad syntax somewhere.

The code does error on this line:

lbxAvailableCodes.Requery

So I commented it out and it does add to the Zip Code table.

Thank you again for your help!

 

[pbaldy]

The space/underscore is VBA's line continuation character, so it was invalid when you didn't drop to the next line. If the code errors on the requery line, I'd suspect that isn't really the name of the listbox.

 

[dan231]

Good to know - thank you.

Do I need to have the requery line in there?

 

[pbaldy]

If you want the listbox to display the newly added value without closing and reopening the form, yes.