Insert new data into a Access table

[C_skelton]

Im using Microsoft Access and need to get my VBA code to insert new data into
a table in my database. The code I have below is what is in my code listings
at the moment;

Private Sub cmdSubmitUser_Click()

Dim stFullname As String
Dim stUsername As String
Dim stPassword As String
Dim stCompanyID As String
Dim stSecLevel As String
Dim mySQL As String 'Access VBA Programming for Dummies

stFullname = "txtUsersFullName"
stUsername = "txtUsername"
stPassword = "txtPassword"
stCompanyID = "combCompanyID"
stSecLevel = "combSecLevel"

mySQL = "INSERT INTO Security (UserName, Password, Security Level, Users
Name, Company_ID)"
mySQL = mySQL + "Values (stUsername, stPassword, stSecLevel, stFullname,
stCompanyID);"


CurrentDb().Execute mySQL

End Sub

The information in my form needs to be taken and inserted into the table.
Any ideas??

 

[Duncan Bachen]

Im using Microsoft Access and need to get my VBA code to insert new data into
a table in my database. The code I have below is what is in my code listings
at the moment;

Private Sub cmdSubmitUser_Click()

Dim stFullname As String
Dim stUsername As String
Dim stPassword As String
Dim stCompanyID As String
Dim stSecLevel As String
Dim mySQL As String 'Access VBA Programming for Dummies

stFullname = "txtUsersFullName"
stUsername = "txtUsername"
stPassword = "txtPassword"
stCompanyID = "combCompanyID"
stSecLevel = "combSecLevel"

mySQL = "INSERT INTO Security (UserName, Password, Security Level, Users
Name, Company_ID)"
mySQL = mySQL + "Values (stUsername, stPassword, stSecLevel, stFullname,
stCompanyID);"

problem #1, you need a space before Values, or after the)

The information in my form needs to be taken and inserted into the table.
Any ideas??

Right now, you're simply putting the name of the control into the
statement, you need to reference the actual value of the control, such as:

mySQL = mySQL + "Values ('" & [Forms]![frmLookup]![txtUsersFullName] &
"', '" & [Forms]![frmLookup]![txtUserName] & "');"

^^^ That's a shortened version, only showing the first two items. You'll
have to increase it to show all 5. Alternately, you can still use your
variables above, but you still need to enclose them in quotes for your
SQL statement because they are strings (not not in quotes if they are
numbers)

stFullname = [Forms]![frmLookup]![txtUsersFullName]
stUsername = [Forms]![frmLookup]![txtUserName]
mySQL = mySQL + "Values ('" & stFullname & "', '" & stUsername & "');"

 

[RD]

Im using Microsoft Access and need to get my VBA code to insert new data into
a table in my database. The code I have below is what is in my code listings
at the moment;

Private Sub cmdSubmitUser_Click()

Dim stFullname As String
Dim stUsername As String
Dim stPassword As String
Dim stCompanyID As String
Dim stSecLevel As String
Dim mySQL As String 'Access VBA Programming for Dummies

stFullname = "txtUsersFullName"
stUsername = "txtUsername"
stPassword = "txtPassword"
stCompanyID = "combCompanyID"
stSecLevel = "combSecLevel"

mySQL = "INSERT INTO Security (UserName, Password, Security Level, Users
Name, Company_ID)"
mySQL = mySQL + "Values (stUsername, stPassword, stSecLevel, stFullname,
stCompanyID);"


CurrentDb().Execute mySQL

End Sub

The information in my form needs to be taken and inserted into the table.
Any ideas??

This bit here:

stFullname = "txtUsersFullName"
stUsername = "txtUsername"
stPassword = "txtPassword"
stCompanyID = "combCompanyID"
stSecLevel = "combSecLevel"

Is passing the names of your controls to the variables. What you want is
something like this:
stFullname = Me.txtUsersFullName
stUsername = Me.txtUsername
stPassword = Me.txtPassword
stCompanyID = Me.combCompanyID
stSecLevel = Me.combSecLevel

Or even:
With Me
stFullname = .txtUsersFullName
stUsername = .txtUsername
stPassword = .txtPassword
stCompanyID = .combCompanyID
stSecLevel = .combSecLevel
End With

"Me" represents the form the controls are on. Also, your SQL will fail unless
you put a space before the word "Values". And, it's better to concatenate using
the & instead of +. Since these values are strings, you'll also need quote
marks around them.

So, your SQL should look like this (I built this in the VBE so watch out for
word wrap):
mySQL = "INSERT INTO Security (UserName, Password, Security Level, Users Name,
Company_ID) " ' <-- Notice space
mySQL = mySQL & "Values ('" & stUsername & "', '" & stPassword & "', '" &
stSecLevel & "', '" & stFullname & "', '" & stCompanyID & "');"

Hope this helps,
RD