Populating a new form with fields from another table

[40690]

I'm a newbie to code writing/editing, posting a thread, and need help modifying an existing complex code. The "after update" event currently works perfectly. Upon the update, a new record with a new "tailcode" is created and auto-populated with about 8-fields of data from a separate table. I have added another 6-fields to this table and would like the "after update" event to also bring these new fields of data into the new form/record. Below is the after update code. About 80% of the way down, "GoTo AssignWO" lists the fields currently being brought over. The last one "ReplyTo" is my best effort to add another field. It is the only one not working and I need to add 5-more fields. I'm unsure if I am providing all the information needed. Please advise and thanks in advance for any guidance/support.


Private Sub Combo2_AfterUpdate()
On Error GoTo Err_Combo2_AfterUpdate
'get change order number
Dim db As Database
Dim rst As Recordset
Dim strsql As String
Dim strJob As String
Dim strTail As String
Dim strLastCO As String
Combo4.Locked = True
strJob = [Combo2]

'''CreateTempTail
DoCmd.SetWarnings False

strsql = "delete * from TempTail"
DoCmd.RunSQL strsql

If Len(strJob) = 4 Then
'append data to Temp
strsql = "INSERT INTO TempTail ( WorkOrder, Tail ) " & _
"SELECT ChangeOrder.[WorkOrder], Str(Right([WorkOrder],4)) AS tail " & _
"FROM ChangeOrder " & _
"WHERE (ChangeOrder.[WorkOrder]) Like" & "'" & strJob & "*'"
DoCmd.SetWarnings False
DoCmd.RunSQL strsql

'select Tail > 0
strsql = "SELECT TempTail.WorkOrder, TempTail.Tail " & _
"FROM TempTail " & _
"WHERE (TempTail.Tail > 0) " & _
"ORDER BY TempTail.Tail DESC; "
Set db = CurrentDb
Set rst = CurrentDb.OpenRecordset(strsql, dbOpenDynaset)
If rst.RecordCount = 0 Then
[Combo4] = "0001"
GoTo AssignWO 'MsgBox "no records"
Exit Sub
Else
rst.MoveFirst
' strTail = rst("Tail")
strTail = Val(rst("Tail")) + 1
If strTail > 9999 Then
MsgBox "Cannot calculate TailCode" 'Last Used Change Order No. is " + Format(strLastCO, ">")
[Combo4] = Null
[Combo4].Locked = False
DoCmd.GoToControl "Combo4"
Exit Sub
End If
End If
If Len(strTail) = 1 Then
strTail = "000" + strTail
End If
If Len(strTail) = 2 Then
strTail = "00" + strTail
End If
If Len(strTail) = 3 Then
strTail = "0" + strTail
End If
End If

If Len(strJob) = 5 Then
'append data to Temp
strsql = "INSERT INTO TempTail ( WorkOrder, Tail ) " & _
"SELECT ChangeOrder.[WorkOrder], Str(Right([WorkOrder],3)) AS tail " & _
"FROM ChangeOrder " & _
"WHERE (ChangeOrder.[WorkOrder]) Like" & "'" & strJob & "*'"
DoCmd.SetWarnings False
DoCmd.RunSQL strsql

'select Tail > 0
strsql = "SELECT TempTail.WorkOrder, TempTail.Tail " & _
"FROM TempTail " & _
"WHERE (TempTail.Tail > 0) " & _
"ORDER BY TempTail.Tail DESC; "
Set db = CurrentDb
Set rst = CurrentDb.OpenRecordset(strsql, dbOpenDynaset)
If rst.RecordCount = 0 Then
[Combo4] = "001"
GoTo AssignWO 'MsgBox "no records"
Exit Sub
Else
rst.MoveFirst
' strTail = rst("Tail")
strTail = Val(rst("Tail")) + 1
If strTail > 999 Then
MsgBox "Cannot calculate TailCode" 'Last Used Change Order No. is " + Format(strLastCO, ">")
[Combo4] = Null
[Combo4].Locked = False
DoCmd.GoToControl "Combo4"
Exit Sub
End If
End If
If Len(strTail) = 1 Then
strTail = "00" + strTail
End If
If Len(strTail) = 2 Then
strTail = "0" + strTail
End If
End If

If Len(strJob) = 6 Then
'append data to Temp
strsql = "INSERT INTO TempTail ( WorkOrder, Tail ) " & _
"SELECT ChangeOrder.[WorkOrder], Str(Right([WorkOrder],2)) AS tail " & _
"FROM ChangeOrder " & _
"WHERE (ChangeOrder.[WorkOrder]) Like" & "'" & strJob & "*'"
DoCmd.SetWarnings False
DoCmd.RunSQL strsql

'select Tail > 0
strsql = "SELECT TempTail.WorkOrder, TempTail.Tail " & _
"FROM TempTail " & _
"WHERE (TempTail.Tail > 0) " & _
"ORDER BY TempTail.Tail DESC; "
Set db = CurrentDb
Set rst = CurrentDb.OpenRecordset(strsql, dbOpenDynaset)
If rst.RecordCount = 0 Then
[Combo4] = "01"
GoTo AssignWO 'MsgBox "no records"
Exit Sub
Else
rst.MoveFirst
' strTail = rst("Tail")
strTail = Val(rst("Tail")) + 1
If strTail > 99 Then
MsgBox "Cannot calculate TailCode" 'Last Used Change Order No. is " + Format(strLastCO, ">")
[Combo4] = Null
[Combo4].Locked = False
DoCmd.GoToControl "Combo4"
Exit Sub
End If
End If
If Len(strTail) = 1 Then
strTail = "0" + strTail
End If
End If
[Combo4] = strTail

GoTo AssignWO

AssignWO:
[6Core] = [Combo2]
[WorkOrder] = [Combo2] + [Combo4]
[WODescription] = [Combo2].Column(1) + "-"
[CMAssigned] = [Combo2].Column(2)
[JobAddress] = [Combo2].Column(3)
[SuptAssigned] = [Combo2].Column(4)
[Groups] = [Combo2].Column(5)
[Bldg#] = [Combo2].Column(6)
[ReplyTo] = [Combo2].Column(7)


''AssignWO:
''[WorkOrder] = [Combo2] + [Combo4]: [WODescription] = [Combo2].Column(1) + "-": [CMAssigned] = [Combo2].Column(2):
''[SuptAssigned] = [Combo2].Column(4): [GROUPS] = [Combo2].Column(5): [Bldg#] = [Combo2].Column(6): [JobAddress] = [Combo2].Column(3):
''[ReplyTo] = [Combo2].Column(7): [6Core] = [Combo2]
'end

''moved up
'''If isTable("TempTail") Then
'''DoCmd.DeleteObject acTable, "TempTail"
'''End If

TailError:
MsgBox "System cannot calculate TailCode; please type it in."
[Combo4] = Null
[Combo4].Locked = False
DoCmd.GoToControl "Combo4"
Exit Sub

Err_Combo2_AfterUpdate:
If Err.Number = "3163" Then '[Combo2] + [Combo4] too large to fit in WO field
MsgBox "Work Order must be 8 characters"
DoCmd.GoToControl "Combo2"
End If
End Sub

 

[theDBguy]

Hi. Welcome to AWF! If you're referring to this line:

[ReplyTo] = [Combo2].Column(7)

Combobox column index starts at zero (0). So, make sure you have at least 8 columns in your combobox. However, I am not sure what you're trying to do is correct. A form could be bound to a table and data from another table shouldn't need to be stored again in another table.

 

[40690]

Yes, the inherited database has many duplicated fields of data in many tables? Please see the attachment for the combo box's columns.

 

[theDBguy]

Yes, the inherited database has many duplicated fields of data in many tables? Please see the attachment for the combo box's columns.

If you're in charge of it now, maybe you have the control to change it for the better.