Using a text box to populate a field

[Adam McReynolds]

I have a form with a text box that I use in conjunction with a command button that needs to create a new record in a table and populate it with a repair ID that is input into the text box. I also want the On Click command to close that current form and open another form to start entering data.

Table is TBL_REPAIRS
Field is RepairID
Form is FRM_MENU
Text Box is txt_new_repair
Button is btn_new_repair
2nd Form is FRM_APU_CUST_INFO (The form I want to go to with the new record)

Any help is greatly appreciated.

 

[Adam McReynolds]

Thanks for the response! That code didn't work for me though. Here is what I have so far:

DoCmd.GoToRecord , , acNewRec
Me![TBL_REPAIRS.RepairID] = Me.txt_new_repair
DoCmd.OpenForm "FRM_APU_CUST_INFO", , , "RepairID= '" & Me.txt_new_repair & "'"
DoCmd.Close acForm, "FRM_MENU"


I know it is botched horribly. The field will be populated this way but I cannot get the form to open on the new record created. It shuffles my new record so that it is not the last record in the form so I can't use "acLast".

 

[Adam McReynolds]

For anyone who finds this helpful I found the fix to my problem. The code creates a new record and populates it with the data entered in the text box and then goes to that record in a new form and closes the previous form. It checks for null entry and duplicate entry as well.

Private Sub btn_new_repair_Click()
If IsNull(Me.txt_new_repair) Or Me.txt_new_repair = "" Then
MsgBox "Please Enter a Repair ID"
Me.txt_new_repair.SetFocus
Cancel = True
Exit Sub
End If

If DCount("RepairID", "TBL_REPAIRS", "RepairID = '" & Me.txt_new_repair & "'") > 0 Then
MsgBox "That Repair ID is already Created. Please Enter a new Repair ID or Search for the Record below in the search field"
Me.txt_new_repair.SetFocus
Cancel = True
Exit Sub
End If

DoCmd.GoToRecord , , acNewRec
Me![TBL_REPAIRS.RepairID] = Me.txt_new_repair
DoCmd.GoToRecord , , acNext
DoCmd.OpenForm "FRM_APU_CUST_INFO", , , "RepairID = '" & Me.txt_new_repair & "'"
DoCmd.Close acForm, "FRM_MENU"
End Sub

 

[Adam McReynolds]

I found a better method for anyone who finds this tread. I needed a billing table to have the same RepairID and create a new billing record and so I used an SQL statement. So this is the same fix as my previous post but will populate 2 or more tables with the repairID.

Private Sub btn_new_repair_Click()
If IsNull(Me.txt_new_repair) Or Me.txt_new_repair = "" Then
MsgBox "Please Enter a Repair ID"
Me.txt_new_repair.SetFocus
Cancel = True
Exit Sub
End If

If DCount("RepairID", "TBL_REPAIRS", "RepairID = '" & Me.txt_new_repair & "'") > 0 Then
MsgBox "That Repair ID is already Created. Please Enter a new Repair ID or Search for the Record below in the search field"
Me.txt_new_repair.SetFocus
Cancel = True
Exit Sub
End If

DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO TBL_REPAIRS (RepairID) Values ('" & Me!txt_new_repair & "');"
DoCmd.GoToRecord , , acNext
DoCmd.RunSQL "INSERT INTO TBL_REPAIRS_BILLING (RepairID) Values ('" & Me!txt_new_repair & "');"
DoCmd.OpenForm "FRM_APU_CUST_INFO", , , "RepairID = '" & Me.txt_new_repair & "'"
DoCmd.Close acForm, "FRM_MENU"
DoCmd.SetWarnings True
End Sub