UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
3 Pages V  1 2 3 >  (Go to first unread post)
   Reply to this topicStart new topic
> New Records In Subform From Another Subform, Access 2016    
 
   
ollyhutsy
post Nov 19 2019, 05:25 AM
Post#1



Posts: 64
Joined: 16-October 19
From: United Kingdom


Hello, I am currently working on a job times sheet for my companies database in order to make job recording more accurate. At the moment I have a job start form which is controlled on document name field. The document could be used in multiple modules within a job. so at the moment i have a module subform with shows the qty of each part used and in what works order number [WO NO]. I then have a job times subform which on the click of a button will enter the document name from the main form. with the following code.

If IsNull(Me.MachinedBy) = False And IsNull(Me.Start_Date) = False Then
Forms![Milling Job Start Form]![Job Times subform].SetFocus
DoCmd.GoToRecord , , acNewRec
Forms![Milling Job Start Form]![Job Times subform].Form![Works Order] = Me.WO_No
Forms![Milling Job Start Form]![Job Times subform].Form![Drawing Name] = Me.Document_Name
Forms![Milling Job Start Form]![Job Times subform].Form![Start Time] = Now()
Forms![Milling Job Start Form]![Job Times subform].Form![OpStart Date] = Now()
Forms![Milling Job Start Form]![Job Times subform].Form![OpMachined By] = Me.MachinedBy
End If

however this document could be used on multiple works order numbers which does not give me an accurate time, as all the time will go under one works order number (whichever one they accessed the form by).

I am wanting to write multiple lines from one subform to the other but I have a hit a wall as i have never done something like this before, I am wanting it to work like:
If IsNull(Me.MachinedBy) = False And IsNull(Me.Start_Date) = False Then
Forms![Milling Job Start Form]![Job Times subform].SetFocus
DoCmd.GoToRecord , , acNewRec '1st job time record
Forms![Milling Job Start Form]![Job Times subform].Form![Works Order] = [Job Start Form Multiple Document Names subform].Form![WO NO] '1st record in the subform
Forms![Milling Job Start Form]![Job Times subform].Form![Drawing Name] = Me.Document_Name
Forms![Milling Job Start Form]![Job Times subform].Form![Start Time] = Now()
Forms![Milling Job Start Form]![Job Times subform].Form![OpStart Date] = Now()
Forms![Milling Job Start Form]![Job Times subform].Form![OpMachined By] = Me.MachinedBy
DoCmd.GoToRecord , , acNewRec '2nd job time record
Forms![Milling Job Start Form]![Job Times subform].Form![Works Order] = [Job Start Form Multiple Document Names subform].Form![WO NO] '2nd record in the subform
Forms![Milling Job Start Form]![Job Times subform].Form![Drawing Name] = Me.Document_Name
Forms![Milling Job Start Form]![Job Times subform].Form![Start Time] = Now()
Forms![Milling Job Start Form]![Job Times subform].Form![OpStart Date] = Now()
Forms![Milling Job Start Form]![Job Times subform].Form![OpMachined By] = Me.MachinedBy
End If

I know it will not work like that but i provided it to give an example of how i want it to work.

any suggestions would be much appreciated.
Go to the top of the page
 
projecttoday
post Nov 19 2019, 06:12 AM
Post#2


UtterAccess VIP
Posts: 11,289
Joined: 10-February 04
From: South Charleston, WV


CODE
DoCmd.GoToRecord , , acNewRec '1st job time record
Forms![Milling Job Start Form]![Job Times subform].Form![Works Order] = [Job Start Form Multiple Document Names subform].Form![WO NO] '1st record in the subform
Forms![Milling Job Start Form]![Job Times subform].Form![Drawing Name] = Me.Document_Name
Forms![Milling Job Start Form]![Job Times subform].Form![Start Time] = Now()
Forms![Milling Job Start Form]![Job Times subform].Form![OpStart Date] = Now()
Forms![Milling Job Start Form]![Job Times subform].Form![OpMachined By] = Me.MachinedBy
DoCmd.GoToRecord , , acNewRec '2nd job time record
Forms![Milling Job Start Form]![Job Times subform].Form![Works Order] = [Job Start Form Multiple Document Names subform].Form![WO NO] '2nd record in the subform
Forms![Milling Job Start Form]![Job Times subform].Form![Drawing Name] = Me.Document_Name
Forms![Milling Job Start Form]![Job Times subform].Form![Start Time] = Now()
Forms![Milling Job Start Form]![Job Times subform].Form![OpStart Date] = Now()
Forms![Milling Job Start Form]![Job Times subform].Form![OpMachined By] = Me.MachinedBy


This suggests a one-to-many relationship. However, in a one-to-many relationship it is not necessary to copy information. A single field is all that is necessary to relate the one to the many. Have you created the tables yet?

Is this document you are trying to create about an employee, a job, or both?

--------------------
Robert Crouser
Go to the top of the page
 
ollyhutsy
post Nov 19 2019, 06:42 AM
Post#3



Posts: 64
Joined: 16-October 19
From: United Kingdom


it is for job time for employees and the job. I don't see how it will work with relationships because there are multiple job times for each job. Also multiple documents with the same name under different works order numbers (the system is a bit messy).
Go to the top of the page
 
ollyhutsy
post Nov 19 2019, 06:50 AM
Post#4



Posts: 64
Joined: 16-October 19
From: United Kingdom


I have attached a picture of my job start form. What i am wanting is the WO NO field values in the subform on the top right to be inputted as two new records in the Job times subform at the bottom, then i can achieve what i want from there. There may also be more than two records in that subform so i need to account for that.

https://www.UtterAccess.com/forum/index.php...id=92140http://[/img]
Attached File(s)
Attached File  Milling_Job_Start_Form.PNG ( 50.6K )Number of downloads: 7
 
Go to the top of the page
 
projecttoday
post Nov 19 2019, 06:53 AM
Post#5


UtterAccess VIP
Posts: 11,289
Joined: 10-February 04
From: South Charleston, WV


QUOTE
I don't see how it will work with relationships


Au contraire, mon ami! They don't call it a relational database for nothing. Relationships are necessary.

This sounds like a time sheet database (pretty common and simple. Maybe there's one here) with a column for a job id.

Correct me if I'm wrong. The relevant specific info that you need to collect:
start time (also the date)
end time
employee
job

--------------------
Robert Crouser
Go to the top of the page
 
projecttoday
post Nov 19 2019, 06:58 AM
Post#6


UtterAccess VIP
Posts: 11,289
Joined: 10-February 04
From: South Charleston, WV


Okay, I see you have made a setup. Does this work at all? Again, are we entering start times and end times?

--------------------
Robert Crouser
Go to the top of the page
 
ollyhutsy
post Nov 19 2019, 07:25 AM
Post#7



Posts: 64
Joined: 16-October 19
From: United Kingdom


This all works however the problem is when there is multiple WO No under 1 document name. I Have already set up relationships but i feel in this instance I cannot achieve what i am trying to do. Op start Date and time fill in on the button press to the current time as long as they have filled in the required data which is machined by. When ok is clicked it will create a job time sheet under the first works order number. however i want to make two records under separate works order numbers.

i am attaching two screen shots, one is what currently is happening and the second is what i want to happen when ok is clicked.
current:
https://www.UtterAccess.com/forum/index.php...st&id=92143
Required:
https://www.UtterAccess.com/forum/index.php...st&id=92142
Attached File(s)
Attached File  Milling_Job_Start_Form.PNG ( 50.6K )Number of downloads: 5
Attached File  required.PNG ( 55.24K )Number of downloads: 7
Attached File  Current.PNG ( 54.16K )Number of downloads: 7
 
Go to the top of the page
 
projecttoday
post Nov 19 2019, 07:36 AM
Post#8


UtterAccess VIP
Posts: 11,289
Joined: 10-February 04
From: South Charleston, WV


If WO 41173-400 is in the main screen then everything in the subforms should pertain to WO 41173-400 and only 41173-400.

It sound to me like you need to remove WO from the main table. Just take WO out of the main table completely.

If this doesn't answer your question type in the table layouts.

--------------------
Robert Crouser
Go to the top of the page
 
ollyhutsy
post Nov 19 2019, 08:08 AM
Post#9



Posts: 64
Joined: 16-October 19
From: United Kingdom


yes I understand that however this cannot change because a parts list is involved in each WO NO. the document name can be used in multiple WO No. however if they start a job from a works order number and the document is used in another works order then all the parts need to be machined at the same time. for example if you have a document name of 41173-400-02. this could be used in 41173-400 and 41173-350. a qty of 2 for each. so when they go onto a jobs to complete list in 41173-400 and click start job i have the subform displaying where else this document needs machining for. this is why there are 2 WO NO's. because they need to be machined at the same time. this system cannot be changed so i need to work around that. i feel that the best way to do this is how i said initially with the job times.
Go to the top of the page
 
projecttoday
post Nov 19 2019, 08:25 AM
Post#10


UtterAccess VIP
Posts: 11,289
Joined: 10-February 04
From: South Charleston, WV


I don't follow you. Can you enter these data manually? And then everything is okay? Your question is how to prepare the data with code? The answer would be to do in INSERT query instead of goto a new record.

--------------------
Robert Crouser
Go to the top of the page
 
ollyhutsy
post Nov 19 2019, 08:28 AM
Post#11



Posts: 64
Joined: 16-October 19
From: United Kingdom


yes I could enter manually but i want the user to do as little as possible. i just want them to fill their name and click ok and the on click event it will create two job times records in the subform which will include the two different WO No, the Same document name and the machinist name
Go to the top of the page
 
ollyhutsy
post Nov 19 2019, 08:37 AM
Post#12



Posts: 64
Joined: 16-October 19
From: United Kingdom


Dim A As String
Dim B As String
Forms![Milling Job Start Form]![Job Start Form Multiple Document Names subform].SetFocus
DoCmd.GoToRecord , , acFirst
A = Forms![Milling Job Start Form]![Job Start Form Multiple Document Names subform].Form![WO NO]
DoCmd.GoToRecord , , acNext
B = Forms![Milling Job Start Form]![Job Start Form Multiple Document Names subform].Form![WO NO]


If IsNull(Me.MachinedBy) = False And IsNull(Me.Start_Date) = False And Me.[Milled Part] = -1 And Me.[Turned Part] = 0 And Me.[WIre Cutting] = 0 And Me.Grinding = 0 And Me.[Further Turning] <> -1 And Me.[Further Milling] <> -1 Then
Forms![Milling Job Start Form]![Job Times subform].SetFocus
DoCmd.GoToRecord , , acNewRec
Forms![Milling Job Start Form]![Job Times subform].Form![Works Order] = A
Forms![Milling Job Start Form]![Job Times subform].Form![Drawing Name] = Me.Document_Name
Forms![Milling Job Start Form]![Job Times subform].Form![Start Time] = Now()
Forms![Milling Job Start Form]![Job Times subform].Form![OpStart Date] = Now()
Forms![Milling Job Start Form]![Job Times subform].Form![OpMachined By] = Me.MachinedBy
Forms![Milling Job Start Form]![Job Times subform].Form![Milling Time] = -1
DoCmd.GoToRecord , , acNewRec
Forms![Milling Job Start Form]![Job Times subform].Form![Works Order] = B
Forms![Milling Job Start Form]![Job Times subform].Form![Drawing Name] = Me.Document_Name
Forms![Milling Job Start Form]![Job Times subform].Form![Start Time] = Now()
Forms![Milling Job Start Form]![Job Times subform].Form![OpStart Date] = Now()
Forms![Milling Job Start Form]![Job Times subform].Form![OpMachined By] = Me.MachinedBy
Forms![Milling Job Start Form]![Job Times subform].Form![Milling Time] = -1
End If

This Code works for two records. However there could be more than two records, I was wondering how i'd incorporate that into this code.
Go to the top of the page
 
cheekybuddha
post Nov 19 2019, 08:41 AM
Post#13


UtterAccess Moderator
Posts: 11,908
Joined: 6-December 03
From: Telegraph Hill


What is the RecordSource of [Job Start Form Multiple Document Names subform]?

What is the RecordSource of [Job Times subform]?

What values do you have in LinkMasterFields, LinkChildFields for the subforms?

--------------------


Regards,

David Marten
Go to the top of the page
 
ollyhutsy
post Nov 19 2019, 08:55 AM
Post#14



Posts: 64
Joined: 16-October 19
From: United Kingdom


they are all linked on Document/drawing name. The first is from my job detail table which is where all the parts are entered under a WO NO (works order number) and the second is my job times table.
Go to the top of the page
 
cheekybuddha
post Nov 19 2019, 09:04 AM
Post#15


UtterAccess Moderator
Posts: 11,908
Joined: 6-December 03
From: Telegraph Hill


What is the name of your 'job detail table'?

What is the name of your 'job times table'?

>> they are all linked on Document/drawing name <<
What are the name of the fields in each table that they are linked on?

--------------------


Regards,

David Marten
Go to the top of the page
 
ollyhutsy
post Nov 19 2019, 09:17 AM
Post#16



Posts: 64
Joined: 16-October 19
From: United Kingdom


Job Detail

Job Times

Job Detail: Document Name

Job Times: Drawing Name
Go to the top of the page
 
projecttoday
post Nov 19 2019, 09:22 AM
Post#17


UtterAccess VIP
Posts: 11,289
Joined: 10-February 04
From: South Charleston, WV


What would you enter manually? I'm not interested in code that doesn't work. Are there always 2 of them? (I doubt it.) What are the table layouts?

--------------------
Robert Crouser
Go to the top of the page
 
GroverParkGeorge
post Nov 19 2019, 09:30 AM
Post#18


UA Admin
Posts: 36,193
Joined: 20-June 02
From: Newcastle, WA


PMFJI:

All of this is sort of beside the point unless we can see and understand the underlying tables. If I can offer a somewhat strained analogy, what you are worrying about is akin to putting interior walls in a house where the foundation may or may not be stable enough to support them in the first place.
Please give us a better picture of the actual tables and all of the fields in those tables.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
ollyhutsy
post Nov 19 2019, 09:31 AM
Post#19



Posts: 64
Joined: 16-October 19
From: United Kingdom


The code works? I am asking for suggestion on how to extend the code to work if there were more than 2 records in that subform without making it painfully long?
Go to the top of the page
 
projecttoday
post Nov 19 2019, 09:33 AM
Post#20


UtterAccess VIP
Posts: 11,289
Joined: 10-February 04
From: South Charleston, WV


See post #10.

--------------------
Robert Crouser
Go to the top of the page
 
3 Pages V  1 2 3 >


Custom Search


RSSSearch   Top   Lo-Fi    11th December 2019 - 07:20 PM