Solved Please Fix my Query

[isladogs]

I have déjà vu!

Wot deja vu all over again?

I'd completely forgotten that thread but thanks for reminding me of the link to the SHA512 code which I'll study in the next couple of days

 

[HillTJ]

Gents/Ladies, although I've elected to use a bound form for my 'registration' ,It still bugs me that I cannot derrive the correct SQL insert into string syntax.

This is my code & the project is for me to learn, but nothing important. I just don't wish to be beaten

And this is an example of the debug.print ;
"INSERT INTO MsysUsers (FirstName, LastName,PWD, PWDDate)"SELECT 'terry' AS firstName,'hill' AS LastName,'(vP.åŸ0è¢P¯Aºiµ#/Ñ' AS PWD,'#25/10/2022#' AS PWDDate;
"
The error message is also attached.

As I said, I'm just trying to learn why the string does not work.
Appreciate any assistance (go easy i'm still very much keen, but an amateur)

Option Compare Database
Option Explicit

Private Sub AddNew_Click()
Dim txtPWD As String
Dim PWD As String
Dim Key As String
Dim strSQL As String

'On Error GoTo Error_Handler
 
    Key = GetKey
      
    txtPWD = fRunRC4(Me.[txtEmail], Key) ' this encrypts the users email address & gets filed 'Key' from a saved database property
    
    PWDdate = Date
        
strSQL = "INSERT INTO MsysUsers (FirstName, LastName,PWD, PWDDate)"
         Debug.Print strSQL
        
strSQL = strSQL & """SELECT '" & Me.[txtFirstName] & "' AS firstName,'"
        Debug.Print strSQL


strSQL = strSQL & Me.[txtLastName] & "' AS LastName,'"
        Debug.Print strSQL
        
strSQL = strSQL & txtPWD & "' AS PWD,'"
        Debug.Print strSQL
        
strSQL = strSQL & "#" & Format(PWDdate, "dd/mm/yyyy") & "#" & "' AS PWDDate;"
        Debug.Print strSQL
 strSQL = """" & strSQL
        Debug.Print strSQL
        
    CurrentDb.Execute strSQL, dbFailOnError
    
    Me.lblInfo.Caption = "New user " & " " & Me.[txtFirstName] & " " & Me.[txtLastName] & " " & "has been successfully added"

exit_proc::
      
    Exit Sub

Error_Handler:

[ATTACH type="full"]104084[/ATTACH]    Call DisplayErrorMessage(Err.Number, "Add New User")
    
    Resume exit_proc
End Sub

 

[cheekybuddha]

The reason is explained in Post #5

In access/jet/ace you must specify a FROM table when using INSERT ... SELECT syntax, and you are then likely to get as many records inserted as there are rows in the table.

To insert a single record use INSERT ... VALUES syntax instead:

' ...
strSQL = "INSERT INTO MsysUsers (FirstName, LastName,PWD, PWDDate)"
strSQL = strSQL & " VALUES ('" & Me.[txtFirstName] & "','" & Me.[txtLastName] & "','" & Me.txtPWD & "',#" & Format(PWDdate, "yyyy-mm-dd") & "#);"
         Debug.Print strSQL
' ...

 

[cheekybuddha]

Note you must also pass dates to SQL in an unambiguous format (irrespective of you local regional date format), which means either US date format (mm/dd/yyyy) or ISO date format (yyyy-mm-dd)

With the date in your example you would have been lucky in using UK date format since it can only be interpreted as 25th October, but you would have come a cropper if you had tried to insert 11th October. It would have been interpreted as 10th November.

 

[HillTJ]

@cheekybuddha , thanks once again. I hate to be beaten by lack of understanding. I've elected to use a bound form, but was still not happy that i could not achieve my initial approach. Thanks will give it a go.

 

[ebs17]

Equivalently, to insert only one record, you can use a Recordset-AddNew. This is somewhat clearer due to the omission of data type-specific formatting.

Dim rs As DAO.Recordset
Set rs = CurrentDb.Openrecordset("SELECT FirstName, LastName, PWD, PWDDate FROM MsysUsers WHERE False", dbOpenDynaset)
With rs
   .AddNew
   .Fields("FirstName") = Me.txtFirstName
   .Fields("LastName") = Me.txtLastName
   .Fields("PWD") = Me.txtPWD
   .Fields("PWDDate") = Date
   .Update
   .Close
End With

Regardless of what else you do, you should definitely have the ability to write into a table.

 

[HillTJ]

@ebs17 , that is the reason i did not give up with my challenge. Thanks.

 

[HillTJ]

To all contributers. Got it working as an unbound form, but elected to go bound for simplicity. Something to be said in letting access manage it'self. Appreciate all recommendations and i achieved my 'learning' goal. Now onto the next challenge....