Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-17-2019, 09:13 AM   #1
40690
Newly Registered User
 
Join Date: Sep 2019
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
40690 is on a distinguished road
Populating a new form with fields from another table

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

40690 is offline   Reply With Quote
Old 09-17-2019, 09:15 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,568
Thanks: 50
Thanked 1,048 Times in 1,029 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Populating a new form with fields from another table

Hi. I'll repeat what I posted in your intro thread.

Welcome to AWF! If you're referring to this line:
Code:
     [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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by theDBguy; 09-17-2019 at 09:28 AM.
theDBguy is offline   Reply With Quote
Old 09-17-2019, 09:25 AM   #3
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,133
Thanks: 15
Thanked 1,570 Times in 1,492 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Populating a new form with fields from another table

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

__________________
Bridge Players Still Know All the Tricks

Last edited by Pat Hartman; 09-17-2019 at 01:51 PM.
Pat Hartman is offline   Reply With Quote
Old 09-17-2019, 09:33 AM   #4
40690
Newly Registered User
 
Join Date: Sep 2019
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
40690 is on a distinguished road
Re: Populating a new form with fields from another table

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.
40690 is offline   Reply With Quote
Old 09-17-2019, 01:53 PM   #5
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,133
Thanks: 15
Thanked 1,570 Times in 1,492 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Populating a new form with fields from another table

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 09-17-2019, 02:27 PM   #6
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,266
Thanks: 0
Thanked 532 Times in 528 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Populating a new form with fields from another table

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.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 09-17-2019, 04:10 PM   #7
40690
Newly Registered User
 
Join Date: Sep 2019
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
40690 is on a distinguished road
Re: Populating a new form with fields from another table

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 is offline   Reply With Quote
Old 09-17-2019, 06:28 PM   #8
40690
Newly Registered User
 
Join Date: Sep 2019
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
40690 is on a distinguished road
I found what I needed using youtube and the below code. Thanks for the guidance.

Me.ReplyTo.Value = Me.Combo2.Column(7)
40690 is offline   Reply With Quote
Old 09-18-2019, 04:20 AM   #9
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,568
Thanks: 50
Thanked 1,048 Times in 1,029 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Populating a new form with fields from another table

Quote:
Originally Posted by 40690 View Post
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.

theDBguy is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Populating a new form with fields from another table 40690 Introduce Yourself 3 09-17-2019 09:30 AM
Auto-populating fields using different table Ceriumde58 Forms 6 01-16-2013 09:17 AM
Populating fields in a form based on another table. daverskully Forms 3 03-11-2008 06:53 AM
Populating fields/table jax Forms 5 06-27-2005 01:39 AM
Populating multiple fields in a table with the same value AlexD General 2 03-13-2003 04:28 AM




All times are GMT -8. The time now is 06:36 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World