Insert in VB doesnt… (A2k)

Topic

New Reply

I’ve got the following code, it worked a few times, and now just won’t. Any ideas?

Private Sub FUIC_AfterUpdate()
Dim strSQL As String, x As String, Response As Integer
If Me.FUIC = “” Then

x = InputBox(“Enter RUC for reserve unit or MCC for active duty”, “Input Value”)
y = InputBox(“Enter unit name/street address, no abbreviations!”, “Input Value”)
z = InputBox(“Enter city of unit, no abbreviations!”, “Input Value”)
w = InputBox(“Enter state of unit, no abbreviations!”, “Input Value”)
v = InputBox(“Enter zip code of unit, no abbreviations!”, “Input Value”)
u = InputBox(“Enter phone number of reserve unit, no parenthesis, hyphens, or spaces!”, “Input Value”)
t = InputBox(“Enter Officer or General. Officer or General only!”, “Input Value”)
s = InputBox(“Enter CONUS, OCONUS, RESERVIST, HAWAII, CUBA, or SPAIN.”, “Input Value”)
r = InputBox(“Enter standard reservist endorsement.”, “Input Value”)

strSQL = “Insert Into tblUnits ([UIC], [Unit_name_street_address], [City], [State], [Zip], [Phone], [CO], [Orders_type], ” & _
“[Endorsement]) values (‘” & x & “‘, ‘” & y & “‘, ‘” & z & “‘, ‘” & w & “‘, ‘” & v & “‘, ‘” & u & “‘, ‘” & t & “‘, ‘” & s & “‘, ‘” & r & “‘);”

CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded

Me.FUIC.Requery
Me.FUIC = x
Else
End If

End Sub

Reply | Quote

Replies

It works if you enter correct values, but it’ll fail if one of the values doesn’t conform to the validation rules or lookup lists, for example if you enter a state abbreviation instead of the full name.
I would never do it this way. Displaying 9 input boxes is very user-unfriendly, and there is no way to let the user select values from a dropdown list.
You should pop up a form with text boxes and combo boxes instead.

Notes:
1) You haven’t declared the variables r through y. This slips by because you don’t require variables to be declared explicitly. You should!
2) The Response variable is meaningless in an After Update event. It is used in the Not In List event of a combo box, and in the On Error event of a form.

Reply | Quote

This is more of a test than anything, thanks for the info, I appreciate it. Now I know why the input failed. Thanks.

Reply | Quote

….and

and I know you know this as the grade/Rank combo thingy Which looks very nice BTW , needs to be tweaked with this:

Private Sub Grade_AfterUpdate()
Me.Refresh
End Sub

Reply | Quote