Adding data using 3 Unbound Textboxes

[Beany]

Hi,

I have 3 unbound textboxes (user,department,location).. i am using a button (AddUser) to insert info from these 3 textboxes into the Table User..

I am using the following code for the button:

Private Sub AddUser_Click()


Dim strSQL As String

strSQL = "INSERT INTO User (User, Department, Location) VALUES (" & Me.User.Value & "," & Me.Department.Value & "," & Me.Location.Value & ");"

DoCmd.RunSQL "strSQL", , acEdit


End Sub

but i am getting the following error:


Compile Error:

Wrong Number of arguments or invalid property assignment

Why is this? Can someone please tell me??

 

[Rabbie]

Hi,

I have 3 unbound textboxes (user,department,location).. i am using a button (AddUser) to insert info from these 3 textboxes into the Table User..

I am using the following code for the button:

Private Sub AddUser_Click()


Dim strSQL As String

strSQL = "INSERT INTO User (User, Department, Location) VALUES (" & Me.User.Value & "," & Me.Department.Value & "," & Me.Location.Value & ");"

DoCmd.RunSQL "strSQL", , acEdit


End Sub

but i am getting the following error:


Compile Error:

Wrong Number of arguments or invalid property assignment

Why is this? Can someone please tell me??

You shouldn't put strSql in quotes. You want to use the value of strSQl not its literal value.
Also looking at Access VBS help for this it seems you shouldn't have the acEdit in the DoCmd.RunSQL "strSQL", , acEdit

Try using DoCmd.RunSQL strSQL

 

[Beany]

thanks mate, Ive tried using:

DoCmd.RunSQL strSQL

but if i click the add button its asking for a PARAMETER VALUE?? why?

 

[Rabbie]

Firstly insert a diagnostic print before the docmd line so you can see what is really in strSQL. ie msgBox(strSQL). Also I think you need single quotes round the values you are inserting. You may need to remove them from numeric fields.

Try this code

Private Sub AddUser_Click()


Dim strSQL As String

strSQL = "INSERT INTO User (User, Department, Location) VALUES ('" & Me.User.Value & "','" & Me.Department.Value & '",'" & Me.Location.Value & "');"

msgBox(strSQL)
DoCmd.RunSQL "strSQL", , acEdit


End Sub

 

[Beany]

Okay Rabbie,

Ive used your code:

and i get the following output:

Insert INTO User (User, Department. Location) VALUES (“,”,”);

after this i get:

You are about to append 1 row (s).

Once you click yes, you can’t use the Undo command to reverse the changes.
Are you sure you want to append the selected rows? YES/NO

If i select Yes, it adds the record but if i say no i get the following runtime error:

Run-time error '2501':

The RunSQL action was canceled....

???

 

[Rabbie]

Right, The diagnostic is telling us that you are not picking up the values you expect to from your form. It looks like Me.user.value is empty.

Try Changing Me.User.value to Me!user.value and the same for the other two values. Actually I don't think you need the .value part as Me!user should give you the current value of the field User on your form.

It might be worth changing some of your names as it is a little confusing to have a table called User, a table field called User and a field on your form called User.

 

[Beany]

The VB editor automatically changes Me!user to Me!User.. i dont know why...??

but the values that i enter in the three textboxes are now adding... but how do i get rid of the message:


You are about to append 1 row (s).

Once you click yes, you can’t use the Undo command to reverse the changes.
Are you sure you want to append the selected rows? YES/NO

 

[Rabbie]

The VB editor will change the field name to match the real one.

To Hide that message you need to change your DB options.

Tools --> Options --> Edit?find

On that screen under confirm uncheck the categories you don't want to confirm. You may need to play around to find the settings you want.

 

[Beany]

Excellent, You're a star Rabbie...

Just one last thing, what code can i include to clear the textboxes after i click on the add button?

 

[Rabbie]

After the DoCmd.RunSQL line

add

Me!user = " "
Me!department = " "
Me"location = " "

Which should clear the boxes for you.

 

[Beany]

You're the Man..

Its working perfectly now! Rabbie thank you

 

[Rabbie]

Happy to help

 

[gibbo82]

So I wanted to piggyback a question on the original question. I used the code given above to add fields from a form into a table upon a user click on a button. After doing that, how can I take those values and add them together as well as go to a new record upon clicking this button.

Here is the code I have to complete this:

Private Sub cmdAddStore_Click()
On Error GoTo Err_cmdAddStore_Click

Dim strSQL As String

strSQL = "INSERT INTO tblStore (Store, Field1, Field2, Field3) VALUES ('" & Me!Store & "','" & Me!Field1 & "','" & Me!Field2 & "','" & Me!Field3 & "');"

DoCmd.RunSQL strSQL

Me.[Total] = Me!Field1 + Me!Field2 + Me!Field3

DoCmd.GoToRecord , , acNewRec

Exit_cmdAddStore_Click:
Exit Sub

Err_cmdAddStore_Click:
MsgBox Err.Description
Resume Exit_cmdAddStore_Click
End Sub

I wanted to have the "Total" field in my table to be the sum of fields 1-3. And upon clicking the button I wanted to go to refresh the form so adding a new field is possible.

I must say I am a newbie, so I appologize for any bad code or flawed logic. But thanks for any help.