Populating a new form with fields from another table

[40690]

I already did my first thread mistake and posted a question in the introduction category. Here's a re-post in the general category.

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. I'll repeat what I posted in your intro thread.

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.

 

[Pat Hartman]

I agree with theDBGuy's assessment. A couple of observations:
1. it is helpful to us if you use code tags so that the code retains its formatting. It is difficult to look at large blocks of left justified text because it is hard to match up the Ifs with the Elses and End Ifs.
2. You do yourself no favors (and anyone who takes over this project when you leave will curse your name) by not using descriptive (descriptive doesn't mean long) control and variable names.
3. When you refer to controls in VBA, use "Me." to qualify the names. This makes it immediately apparent that the code is referring to a control rather than a variable in addition to being more efficient.

Combo4.Locked = True
strJob = [Combo2]

becomes:

Me.Combo4.Locked = True
strJob = Me.Combo2

But who knows what combo2 and combo4 are? maybe the shadow knows

 

[40690]

combo2 is the first 6-digits of a work order and combo4 are the numerical next in-line tail-code. Together/combined they create a new unique 8-digit work order number.

 

[Pat Hartman]

I don't really care what combo2 and combo4 are. I was pointing out that WHATEVER they are, they aught to have some meaningful name.

 

[June7]

I agree and also ask - why are you duplicating data between tables? Main principle of relational database is to not duplicate data. Save primary key of source table as foreign key into related dependent table. Retrieve related info in query that joins tables.

 

[40690]

Thanks for the advice.

Would you believe the expert who created our dysfunctional functioning system promoted out last January. I've stepped-up as a fill-in until a new hire (not anytime soon) can be obtained. I would appreciate any guidance that keeps the existing unnecessary/idiotic duplication of 8-fields and increases it to 12-fields of duplication. This would save 1000's of keying in entries and make it look like I know what I'm doing, even if it is pretending. Thanks in advance.

 

[40690]

I found what I needed using youtube and the below code. Thanks for the guidance.

Me.ReplyTo.Value = Me.Combo2.Column(7)

 

[theDBguy]

I found what I needed using youtube and the below code. Thanks for the guidance.

Me.ReplyTo.Value = Me.Combo2.Column(7)

Hi. Glad to hear you got it sorted out. Good luck with your project.