CODEWITHSUNDEEP
sqlvbams-access
Apix_D
Apix_D

Reputation: 1101

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? :D

Could somebody help me?

thanks.

Upvotes: 0

Views: 90

Answers (3)

Vlado
Vlado

Reputation: 888

You do not need to use stored procedure for that simple task. First of all make the UserID field AutoNumber. Than (if the data resides in SQL sever) link the table in Access. Now you have 2 options:

  1. The most natural option is to set form's RecordSource to your table ("SELECT * FROM user") and you do not need to code almost anything. Just add button which creates a new record and button which saves it. Use commands Me.AddNew and DoCmd.RunCommand acCmdSaveRecord in buttons _Click events.

  2. If for some reason you want to use unbound controls use this code to insert a new row:

Sub Befehl85_Click()
   Dim SQLText As String
   SQLText = " INSERT INTO user " & _
       "(UserForename,UserSurname,Mail) VALUES " & _
       "('" & Me.UserForename & "','" & Me.UserSurname & "', '" & Me.test2 & "');"
   DoCmd.RunSQL SQLText 
End Sub

I need to tell you that this belongs to the basics in Access and I recommend you read some tutorials or watch YouTube.

Upvotes: 0

SunKnight0
SunKnight0

Reputation: 3351

Assuming that by "formular" you mean form this is what you need:

Sub Befehl85_Click()

DoCmd.RunSQL "INSERT INTO user (UserID,UserForename,UserSurname,Mail) VALUES (" & DMax("UserID","user")+1 & ",'" & Me.UserForename & "','" & Me.UserSurname & "','" & Me.Mail & "')"

End Sub

As others have said you should convert the UserID to an AutoNumber and not manually increment. then you can skip that part.

Upvotes: 0

Kostas K.
Kostas K.

Reputation: 8518

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

Upvotes: 2

Related Questions