Access add new SQL data

I would like to add some Data in my SQL table…

Thats the actual build. – table User (Saves Useraccess data with column UserId, UserForename,UserSurname, Mail) – Formular add new User

My formular has 3 TextFields for UserForename, UserSurname, Mail and a button for adding the details.

By clicking the button the following code should be executed…

Sub Befehl85_Click()

Dim SQLText As String

SQLText = " INSERT INTO user " & _
"(UserID,UserForename,UserSurname,Mail) VALUES " & _
"('SELECT MAX(UserID)+1','UserForename','UserSurname', 'Test2');"

End Sub

The UserID should be filled automatically with the next free ID space (descending numbered)

I am looking for a solution like… 3 hours? 😀

Could somebody help me?

thanks.

Answer

As advised in comments by krish KM, change the UserID to AutoNumber and you won’t have to worry about this field.

Then setup a query that you can pass the import values as parameters.

Query with parameters:

PARAMETERS [prmForename] Text (255), [prmSurname] Text (255), [prmMail] Text (255);
INSERT INTO User( UserForename, UserSurname, Mail )
SELECT [prmForename], [prmSurname], [prmMail];

Calling the above query in VBA:

With CurrentDb().QueryDefs("QueryName")
    .Parameters("[prmForename]").Value = [Value from TextBox] 
    .Parameters("[prmSurname]").Value = [Value from TextBox] 
    .Parameters("[prmMail]").Value = [Value from TextBox] 
    .Execute dbFailOnError
End With