Insert into ... problem

[bvnfevs]

Hi all.
I'm having trouble creating a form with which you can add fields to a table.
I let the user fill in two textboxes and then clicking a button. A messagebox appears that ask the user to confirm the values. When clicking "Yes" i want the "Insert into"-statement to run. But I can't get to work. What am I doing wrong? You can see the code I'm using below.

//Anders

Private Sub AddButton_Click()
If
Dim Msg, Style, Title, Response
Msg = "Model No.:" & AddNewModel.Value & (Chr(13)) & _
"Part No.:" & AddNewPart.Value & (Chr(13)) & _
(Chr(13)) & _
"Are these the correct values of the Gear Box" & (Chr(13)) & _
"that You want to add?"
Style = vbYesNo + vbWarning + vbDefaultButton2 ' Define buttons.
Title = "Add New Gear Box" ' Define title

' Display message.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.

' Insert values Into Database

Dim SQL As String
SQL = "INSERT INTO Boxes (ModelNumber, PartNumber) VALUES ('78', '13')"

DoCmd.RunSQL SQL

ElseIf Response = vbNo Then
Me.AddNewModel.SetFocus
End If
End If
Exit_AddButton_Click:
Exit Sub
End Sub

 

[GHolden]

Hi,

If ModelNumber and PartNumber are numeric data types try removing the ' from your SQL

ie.

SQL = "INSERT INTO Boxes (ModelNumber, PartNumber) VALUES (78, 13)"


There are two ways to write error-free programs; only the third one works.

 

[bvnfevs]

The numbers 78 and 13 are just something I typed in to check if it worked (not using the textboxes at this stage). The textboxes will contain text and not only numbers.

It seems to me that the RunSQL-command doesn't run at all. Nothing happens when I click the Add-button. If I want something to happen "after" RunSQL-command that won't work either.

Any suggestions?

 

[GHolden]

You seem to have an If before your Dim's which looks wrong, and the ElseIf is not required, Else will do (you only have the choice of Yes or No).

Try this...

Private Sub AddButton_Click()
Dim Msg, Style, Title, Response

Msg = "Model No.:" & AddNewModel.Value & (Chr(13)) & _
"Part No.:" & AddNewPart.Value & (Chr(13)) & _
(Chr(13)) & _
"Are these the correct values of the Gear Box" & (Chr(13)) & _
"that You want to add?"
Style = vbYesNo + vbWarning + vbDefaultButton2 ' Define buttons.
Title = "Add New Gear Box" ' Define title

' Display message.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.

' Insert values Into Database

Dim SQL As String
SQL = "INSERT INTO Boxes (ModelNumber, PartNumber) VALUES ('78', '13')"

DoCmd.RunSQL SQL

Else
Me.AddNewModel.SetFocus
End If

Exit_AddButton_Click:
Exit Sub
End Sub

You will still get an error from the ' in the insert if the fields are Numeric type.





There are two ways to write error-free programs; only the third one works.

 

[bvnfevs]

No, sorry. That didn't do it.
The problem is that I don't even get a error-message, nothing happens. After the RunSQL I want a certain object to become visible, but that doesn't happen either. That only works if I delete the RunSQL-command.

???

 

[GHolden]

Er... okay.

What happens if you simply do the following?


Private Sub AddButton_Click()
Dim SQL As String

SQL = "INSERT INTO Boxes (ModelNumber, PartNumber) VALUES (78, 13)"

DoCmd.RunSQL SQL

End Sub


There are two ways to write error-free programs; only the third one works.

 

[bvnfevs]

Absolutely nothing. I don't get this.
The table Boxes that I wan't to add data to contains of three columns: ID (AutoNumber), ModelNumber (Text) and PartNumber (Text). I've tried to add values for the ID-column, like this:

SQL = "INSERT INTO Boxes (ID, ModelNumber, PartNumber) VALUES ('', '78', '13')"

But that won't work either. I shouldn't have to do this, since ID is AutoNumber, right?

 

[GHolden]

I don't get it either.

You are right about the autonumber. I just tried this...

Dim strSQL As String
strSQL = "INSERT INTO tblName (Name, Age) VALUES ('Graham','34') "

DoCmd.RunSQL strSQL

Into a table with 3 fields (the first being an autonumber) and it worked fine.

Is that bit of code definately being executed?

If you do the following...

Private Sub AddButton_Click()
Dim SQL As String

SQL = "INSERT INTO Boxes (ModelNumber, PartNumber) VALUES (78, 13)"

MsgBox SQL
DoCmd.RunSQL SQL

End Sub

Do you see the message box??


There are two ways to write error-free programs; only the third one works.

 

[bvnfevs]

Yes, the MessageBox appears with the SQL-query. So that bit executes.
I was thinking; is there anything in the Object Library that I have forgotten to "check" or "uncheck"?

 

[GHolden]

There isn't any library needed for this functionality.

You could try this....

Dim db As Database
Dim strSQL As String

Set db = CurrentDb

strSQL = "INSERT INTO Boxes (ModelNumber, PartNumber) VALUES ('78', '13')"

db.Execute (strSQL)

Set db = Nothing


Make sure you have a reference to Microsoft DAO Library


There are two ways to write error-free programs; only the third one works.

 

[bvnfevs]

That didn't work either. I have tried it before. I tried to create a new form and pasted the code into that form and it worked fine.
Can it be that the table Boxes is open when the code is being executed? (The form uses the table Boxes on another place in the form). Maybe the table Boxes is locked for manipulation when the form is open. Can that be a problem? If it is, is there a way to get around it?

 

[GHolden]

Is the open form bound to table Boxes? If it is there is no reason why the code shouldn't work, however you wouldn't see the changes until you requery the form (so it would appear that the update hadn't worked).

(ps. sorry for the delay in response)




There are two ways to write error-free programs; only the third one works.