VBA-Deleting fields (Input Problem)

[daoxx]

Hi
I've a table and a form. The table is named Cars and it has a field N_Car, which is where a ID number is stored (made of 5 digits, 12345).
cCar is a combo box with the car IDs listed.

I have this in a button on the form:

Private Sub cmdApV_Click()
Dim sql1 As String
Dim c As Integer
sql1 = "DELETE * FROM Cars WHERE [N_Car]='" & c & "'"
c = InputBox("Insert the ID of the car you want to remove.","Remove Car")
DoCmd.RunCommand (acCmdSelectAllRecords)
DoCmd.RunSQL sql1
Me.cCar.Requery
End Sub

It gets me an Overflow error. If I do "Dim c" (without "As Integer" it asks me for a parameter, when I've already input it with the input box.

Please help me :\

[mshmyob]

You have some minor problems with the code. Try this

Dim sql1 As String
Dim c As Integer
c = InputBox("Insert the ID of the car you want to remove.", "Remove Car")
sql1 = "delete from Cars where N_Car= " & c
DoCmd.RunSQL sql1
Me.cCar.Requery
 

Make sure in the future you ask for your variable before you create the SQL string.

cheers,

Hi
I've a table and a form. The table is named Cars and it has a field N_Car, which is where a ID number is stored (made of 5 digits, 12345).
cCar is a combo box with the car IDs listed.

I have this in a button on the form:

Private Sub cmdApV_Click()
Dim sql1 As String
Dim c As Integer
sql1 = "DELETE * FROM Cars WHERE [N_Car]='" & c & "'"
c = InputBox("Insert the ID of the car you want to remove.","Remove Car")
DoCmd.RunCommand (acCmdSelectAllRecords)
DoCmd.RunSQL sql1
Me.cCar.Requery
End Sub

It gets me an Overflow error. If I do "Dim c" (without "As Integer" it asks me for a parameter, when I've already input it with the input box.

Please help me :\

[daoxx]

That gave me an Overflow error, but after deleting "As Integer" everything ran fine. Thanks :)

[Scott Price]

Deleting 'As Integer' defaults to a Variant data type for your variable. The overflow error comes because you were getting a result which exceeded the range for which the Integer data type is valid (-32,767 to 32,766 more or less). It's a better programming practice to specifically type cast your variables, rather than letting VBA 'choose' which is what happens with the Variant...

Making it a Long data type should take care of the overflow error.

Regards,
Scott

[daoxx]

Okay, thank you for teaching me something new :D

Deleting 'As Integer' defaults to a Variant data type for your variable. The overflow error comes because you were getting a result which exceeded the range for which the Integer data type is valid (-32,767 to 32,766 more or less). It's a better programming practice to specifically type cast your variables, rather than letting VBA 'choose' which is what happens with the Variant...

Making it a Long data type should take care of the overflow error.

Regards,
Scott

[Scott Price]

Not a problem :-)

I usually err on the side of 'too much' information rather than too little, but usually there's a little bit of good in it.

Regards,
Scott