Ok this is the problem that I am running into now. I finally got the code to work to change the cab# on the main form, but I have a subform that has multiple inventories of each of the vehicles. The information in those subforms are stored in a table called Vehicle Inventory with 2 columns that matter, inventoryID (primary key, autonumber) cab# (indexed, allow multiples). I want to find all of the records that have strOldCAB in the cab# column and replace it with strNewCAB. I tried this with forms but failed because when I did the "DoCmd.FindRecord strOldCAB" I believe it was searching the vehicle inventoryID column and not the cab# column. Below is the code that I failed with (all of the lines that are commented out are the missing link) is there any other way to do this?
Code:
Private Sub CAB_KeyDown(KeyCode As Integer, Shift As Integer)
Dim strNewCAB As String
Dim strOldCAB As String
Dim strSearch As String
If EditCAB = False Then
If MsgBox("Are You Sure You Would Like To Change This Cab number?", _
vbYesNo + vbQuestion + vbDefaultButton2, _
"Please Confirm:") = vbYes Then
strOldCAB = CAB.Value
EditCAB = True
strNewCAB = InputBox("What is the new cab number?", "New Cab number")
If strNewCAB = "" Or strNewCAB = Empty Then
MsgBox "No Input Provided", vbInformation, "Required Data"
EditCAB = False
CAB.SetFocus
Me.Refresh
Exit Sub
End If
If strNewCAB > 999 Then
MsgBox "Sorry, This Cab Number Is Incorrect", _
vbOKOnly, "Important Information"
EditCAB = False
CAB.SetFocus
Me.Refresh
Exit Sub
End If
Me.Requery
DoCmd.ShowAllRecords
DoCmd.GoToControl ("CAB")
DoCmd.FindRecord strNewCAB
strSearch = CAB.Text
If strSearch = strNewCAB Then
Me.Requery
DoCmd.ShowAllRecords
DoCmd.GoToControl ("CAB")
DoCmd.FindRecord strOldCAB
MsgBox "" & strSearch & " Is Taken - Please Try Again.", _
, "Important Information"
Exit Sub
Else
DoCmd.GoToControl ("CAB")
DoCmd.FindRecord strOldCAB
txtoldcab.Visible = True
txtoldcab.SetFocus
txtoldcab.Locked = False
txtoldcab.Value = strOldCAB
txtoldcab.Locked = True
CAB.SetFocus
txtoldcab.Visible = False
CAB.Value = strNewCAB
CAB.Locked = True
EditCAB = False
' DoCmd.OpenForm ("vehicle inventory")
' DoCmd.Requery
' CAB.SetFocus
' DoCmd.FindRecord "strOldCAB", acEntire, , , , acCurrent, True
' strSearch = CAB.Value
' Do
' If strSearch = strOldCAB Then
' CAB.SetFocus
' CAB.Value = strNewCAB
' DoCmd.RunCommand acCmdSaveRecord
' DoCmd.Requery
' CAB.SetFocus
' DoCmd.FindRecord "strOldCAB", acEntire, , , , acCurrent, True
' strSearch = CAB.Value
' End If
' Loop While strSearch = strOldCAB
' DoCmd.Close acForm, "Vehicle Inventory"
Me.Requery
DoCmd.FindRecord strNewCAB
DoCmd.RunCommand acCmdSaveRecord
MsgBox "Cab #" & strOldCAB & " Has Been Changed To Cab #" & strNewCAB & "", _
, "Important Information"
End If
End If
End If
End Sub