How to make bound cbo be blank Form open (1 Viewer)

[Zydeceltico]

When the new record is added via th unbound txtbox, it is really adding a new record for an Inspection (or so it seems logically). So adding to tblCoils is only part of what is required - this seems to be a new JOB and some other fields may be required that differ from a previous job where this Coil was used. This is the logic I'm asking about. If this is a new Inspection for a new Job, how would/do you enter the accompanying data values?

It is adding new coils to tblCoils
tblCoils

CoilNumber_PK	CoilNumber	MatCert	CoilSupplierID_FK	CoatingID_FK	CoatingSupplier_FK	Gauge	Supplier	Acoustic Y/N	Perforator	PerforationLOCCorrect	Separator	Insert	CoilWidth	Levelled	WeightIn	WeightOut	Issues	Rejected	RejectReason	RejectionStatus	Notes	Photos
48​	P24956		0​	4​	1​	20		Yes	Plum		Paper	no	21​		0​	0​	N/A
49​	P24936		0​	4​	1​	20		No	Not Perforated		Paper	no	21.008​		0​	0​
50​	z98989		0​	0​	0​								0​		0​	0​
51​	QWASDE		0​	0​	0​								0​		0​	0​
52​	zzzxxx		0​	0​	0​								0​		0​	0​

Are you beginning your "tests" at the very beginning from Main Menu and clicking on the "Inspections" button every time?
Because.....all inspections begin there. The first step (and unavoidable required step) is choosing a job in frmInspectionEvent before selecting which type of inspection you are going to perform. That step is where Job_ID from tblJobs is entered in tblInspectionEvent and only after that is frmInspectMill opened and the question of Coil Number is addressed. If you try to test by opening frmInspectMill without going from the beginning tblInspectionEvent doesn't receive the Job_ID.

 

[Zydeceltico]

That is why I thought it might be helpful to look at the SQL in post #47 because it kind of spells out how tables are related to each other. and what is drawn from each.

 

[jdraw]

I think I have hit on one obstacle and I should have spotted this earlier??
The record source of the frmInspectMill is

SELECT tblInspectMill.*
, tblInspectionEvent.Notes AS Notes_tblInspectionEvent
FROM tblInspectionEvent INNER JOIN tblInspectMill ON
tblInspectionEvent.InspectionEvent_PK = tblInspectMill.InspectionEvent_FK;

So, we need to determine exactly where CoilNumber affects this???
And what role tblCoils plays.
There are table/relationship issues in my view. The business rules are not clear and may not have been recorded anywhere for testing/vetting.
In my sample with text312, I had a separate unbound textbox whose value could be used to identify the record to display when the form was reopened, but more has to be done to ensure these other table values that populate the form.

 

[Zydeceltico]

I think I have hit on one obstacle and I should have spotted this earlier??
The record source of the frmInspectMill is

SELECT tblInspectMill.*
, tblInspectionEvent.Notes AS Notes_tblInspectionEvent
FROM tblInspectionEvent INNER JOIN tblInspectMill ON
tblInspectionEvent.InspectionEvent_PK = tblInspectMill.InspectionEvent_FK;

So, we need to determine exactly where CoilNumber affects this???
And what role tblCoils plays.
There are table/relationship issues in my view. The business rules are not clear and may not have been recorded anywhere for testing/vetting.
In my sample with text312, I had a separate unbound textbox whose value could be used to identify the record to display when the form was reopened, but more has to be done to ensure these other table values that populate the form.

What can I provide to clarify business rules?

Also - I may be offline soon - -the weather just called for golf ball-size hail and a possible tornado in the next half hour. Never a dull moment.

 

[jdraw]

Ok. I looked at the Example query. And have attached the Tables and Relationships. It will help with questions and understanding. Just saw your latest post--yes I'm going to have supper and will get back at another time. Keep safe.
.

 

[Zydeceltico]

There are table/relationship issues in my view. The business rules are not clear and may not have been recorded anywhere for testing/vetting.
In my sample with text312, I had a separate unbound textbox whose value could be used to identify the record to display when the form was reopened, but more has to be done to ensure these other table values that populate the form.

Is the primary issue that I have included tblInspectionEvents.InspectionNotes on frmInspectMill? My thinking was that I need a notes field for all types of inspections and I was considering normalization of a notes field to tblInspectionEvents instead of each inspecion table having its own notes field but if that is the issue it is a simple thing to put the notes fields back in each separate inspection table and remove it from tblInspectionEvents which I believe would simplify the record source for frmInspectMill I think.

 

[jdraw]

Possibly, but I think the issue is pinning down the entities/tables in a way that is meaningful to your business. It seems to me (who is miles away form the details) that an Inspection relates to the Coil used on a Job. In quasi-database jargon that is something like

Job---->Inspections<----Coils
       of processed
       coil(s) for job

It seems an Inspection is one of several types (welding, paint...) of quality checks performed on coil samples in jobs. At this time my thinking is let's get back to the design rather than coding modifications to make something work. Reviewing some sample scenarios and test cases against the model (tables and relationships) should clarify the business rules/facts.

 

[Zydeceltico]

We made it through the hail! Yeah!

So..........................

It seems an Inspection is one of several types (welding, paint...) of quality checks performed on coil samples in jobs. At this time my thinking is let's get back to the design rather than coding modifications to make something work. Reviewing some sample scenarios and test cases against the model (tables and relationships) should clarify the business rules/facts.

Almost - you almost have it!

It seems an Inspection is one of several types (welding, paint...) of quality checks...

But NOT

performed on coil samples in jobs.

Only SOME types of inspections are "performed on coil samples in jobs." Mill and Line Stop inspections = YES (always); Welding, Fabricating, Painting, and everything else types of inspections = No (never).

It seems to me (who is miles away form the details) that an Inspection relates to the Coil used on a Job.

Almost. An Inspection relates to an inspector deciding to perform an inspection event which could be of any type (see in bold above). As a business rule this statement of yours, " an Inspection relates to the Coil used on a Job", needs to be restated to say: The recordable characteristics of a coil, including coil number, relates to a Mill or Line Stop Inspection used on a job and does not relate to a Welding, Fabricating, or Painting inspection.


In which case this:

Job---->Inspections<----Coils
       of processed
       coil(s) for job

would look more like this:

Inspections------->Job-------> IF Mill or Line Stop inspection THEN --------->Coils
ELSE----------> NOT COILS
END IF
[\code]

[QUOTE="jdraw, post: 1680584, member: 31021"]Possibly, but I think the issue is pinning down the entities/tables in a way that is meaningful to your business.[/QUOTE]

I am totally game. What's next?

 

[jdraw]

Glad you made it thru the hail. Good feedback.

 

[Zydeceltico]

Glad you made it thru the hail. Good feedback.

and.............after another marathon day..........I almost have my situation figured out!

But--------the last piece of the puzzle.

If I have two open forms, is there a way to pass a value from a textbox on the form I am working on (Form1) to a textbox on Form2 (which is already open) when I click a button that closes Form1 without using a global variable?

I know how to do it with a global variable but am trying to avoid that route.

Since Form2 is already open I can't use OpenArgs can I?

 

[jdraw]

I think I saw a thread on this just recently --found it take a look and see if it helps. There are some code snippets that you might try. What's the problem with global variable--I would probably try a TempVar to preserve the value from form1 in order to paste/assign it into textbox on form2

 

[Zydeceltico]

I think I saw a thread on this just recently --found it take a look and see if it helps

I've been searching but haven't found it. Do you have a link?

Thanks!

Tim

 

[Zydeceltico]

I've been searching but haven't found it. Do you have a link?

Thanks!

Tim

Ok - so a simple form.control reference did the trick.

I think I'm almost there.

 

[jdraw]

This is the link I mentioned --forgot to hit the INSERT button.....

 

[Zydeceltico]

This is the link I mentioned --forgot to hit the INSERT button.....

So now I have managed to pass my new value from a bound textbox on Form2 back to an unbound textbox on Form1 and Form2 closes because I am done with it.

Now that the unbound textbox on Form1 has a value in it - I need to click a "Save and Close" button on Form 1 to record all of the data I have collected on it - but of course my textbox is unbound so it is not storing/delivering it's new value to - anywhere.

And this textbox is - of course - txtCoilNumber which is now showing the string value of CoilNumber but what I really to do is record the numeric value of the ID (CoilNumber_PK) that was created when I entered a new coil in Form2 and closed Form2.

I'm thinking that perhaps on Form 1, I could add an invisible textbox that is bound to tblCoils.CoilNumber_PK. I was then thinking that on the "Click" event of my "Save and Close" button on Form1 I would have some sort of search function or query that would grab the string value in the unbound txtCoilNumber, "go to" tblCoils; find that string in fieldCoilNumber; and and bring the correct numeric value for CoilNumber_PK back to Form1 and set the value of my invisible textbox (txtCoilNumber_PK) to the retrieved value before closing Form1.

Is that possible and would DLookup work for the search function? Or is there a better way and I am making this too complicated?

 

[jdraw]

In the code I pasted the other day, I could get the CoilNumber from a textbox and insert it as a new record in tblCoils. (That code can be modified to get CoilNumber_PK). BUT that was not the underlying issue. The problem I found was that the tables involved(the recordsource for the form) was not tblCoils but

SELECT tblInspectMill.*
, tblInspectionEvent.Notes AS Notes_tblInspectionEvent
FROM tblInspectionEvent INNER JOIN tblInspectMill ON
tblInspectionEvent.InspectionEvent_PK = tblInspectMill.InspectionEvent_FK;

So the real question is - when you load a Coil, how do you populate the remainder of the form to capture data for the underlying table(s).

I'm still in "house arrest" so can look at your latest code/database and the answer to the question above.

 

[Zydeceltico]

In the code I pasted the other day, I could get the CoilNumber from a textbox and insert it as a new record in tblCoils. (That code can be modified to get CoilNumber_PK). BUT that was not the underlying issue. The problem I found was that the tables involved(the recordsource for the form) was not tblCoils but

SELECT tblInspectMill.*
, tblInspectionEvent.Notes AS Notes_tblInspectionEvent
FROM tblInspectionEvent INNER JOIN tblInspectMill ON
tblInspectionEvent.InspectionEvent_PK = tblInspectMill.InspectionEvent_FK;

So the real question is - when you load a Coil, how do you populate the remainder of the form to capture data for the underlying table(s).

I'm still in "house arrest" so can look at your latest code/database and the answer to the question above.

Believe it or not - I now have that part figured out. So proud of myself. I'm able to input a new coil with no record in tblCoils, open frmCoilStatus and enter all the relevant data, save, close return to frmInspectMill and have the new coil recorded in tblCoils and also brought the coil ID (CoilNumber_PK) back to frmInspectMill. That works great now.

I changed the code of the AfterUpdate event of txtCoilNumber on frmInspectMill to this:

Private Sub txtCoilNumber_AfterUpdate()
          Dim tempCoilNo As String
          'new coil number check
        If Len(Me.txtCoilNumber & "") = 0 Then
            MsgBox "You must enter a Coil Number", vbInfo
            Me.txtCoilNumber.SetFocus
        End If
          'check if coil is partial/has been used before
        tempCoilNo = Me.txtCoilNumber
        If DCount("CoilNumber", "tblCoils", "CoilNumber ='" & tempCoilNo & "'") > 0 Then 'coil exists
            MsgBox " Coil " & tempCoilNo & "  has been used previously. This is a partial coil"
        Else  'brand new coil so add it to tblCoils
            If MsgBox("Coil not found. Would you like to add this coil?", vbQuestion + vbYesNo, "Duplicate") = vbYes Then
'            CurrentDb.Execute "Insert into tblCoils (CoilNumber_pk,CoilNumber) Values (" _
'            & DMax("CoilNumber_PK", "tblCoils") + 1 & ",'" & tempCoilNo & "')", dbFailOnError
            DoCmd.OpenForm "frmCoilStatus", , , , acFormAdd, , tempCoilNo
            txtCoilNumber.Value = Null
            Forms![frmCoilStatus]![cmdAddCoil].Enabled = False
            Forms![frmCoilStatus]![cmdUndoCoilStatus].Enabled = False
            Forms![frmCoilStatus]![cmdSaveCoilStatus].Enabled = False
            Forms![frmCoilStatus]![cmdCoilCertLink].Enabled = False
            Forms![frmCoilStatus]![cmdJobsFolderLink].Enabled = False
            Forms![frmCoilStatus]![cboMoveTo].Enabled = False
            'MsgBox tempCoilNo
            End If
            
       End If
End Sub

Notice that I commented out this line: "CurrentDb.Execute "Insert into......." which would make a new entry in tblCoils but was blocking me in other ways.

I ended up passing the new coil number to frmCoilStatus with OpenArgs and then controlling the coil form some more to guide the user. Then I pass the CoilNumber_PK and CoilNumber back to frmInspectMill - - and it works!

But now I have different challenge in a different part of the code above. We have it figured out when the user enters a coil that doesn't exist.

But now I need to figure out how to actually record the CoilNumber_PK in tblInspectMill when a coil DOES already exist which wouldn't be an issue except that txtCoilNumber is unbound.

Basically, after the line above:

        If DCount("CoilNumber", "tblCoils", "CoilNumber ='" & tempCoilNo & "'") > 0 Then 'coil exists
MsgBox " Coil " & tempCoilNo & "  has been used previously. This is a partial coil"

I need to get the CoilNumber_PK from tblCoils for the coil number entered and place that value in txtCoilNumber_PK which is bound and I have made visible at the bottom of the form in order to check it.

Would I use DLookup for that?

I've attached an updated version of the db. If you have time to try it that'd be great! Just remember to start at the Main Menu, select "Inspections" and go from there - entering any made up coil number (e.g. R99999 or something like that).

Hope "house arrest" is going well!

Tim

 

[jdraw]

I was having my own issue with Windows and Linux dual boot- not resolved.
I looked at your code briefly and do not follow your logic. I can see if you enter a coil number and it is a new coil you would add that record to tblCoil (which is what we did before) but I'm not following how you are adding the additional info for the coil or the tables related to inspection (InspectMill and InspectionEvent) which underlie the form frmInspectMill .
CoilNumber_PK is an autonumber so you don't have to insert it, Access will do that automatically.
I'll look more into your specific question re getting the value into the text box at " the bottom of the form ".

If it's working for you that's great. I keep getting OpenForm "frmCoilStatus" event was cancelled???

Think I may be goin' stir crazy --nothing is working today.....

OK I tried it several times - got everything from FormOpen cancelled to you can't assign a value...
BUT when I added the coil and started to select values from your combo boxes then it worked and assigned the value to the textboxes for Coil_PK and InspectionEvent_PK.

 

[Zydeceltico]

I looked at your code briefly and do not follow your logic. I can see if you enter a coil number and it is a new coil you would add that record to tblCoil (which is what we did before) but I'm not following how you are adding the additional info for the coil or the tables related to inspection (InspectMill and InspectionEvent) which underlie the form frmInspectMill .

The whole process starts at The Main Menu by clicking the "Inspections" button. When that button is clicked it opens frmInspectionEvent which is bound to tblInspectionEvent. The very first choice on that form is to choose a job number in the header. Job number is bound to tblJobs. There is a host of relevant data in tblJobs. And at this point there is a new record in tblInspectionEvent that has the date/time of the event, the job number ID, the operator's name, and the inspector's name.

Then clicking the "Mill Inspections" opens form frmInspectMill which instantiates a new record in tblInspectMill. The click event of that button sets the value of a global value to the newly created InspectionEvent_PK - and passes it through to frmInspectMill and subsequently to tblInspectMill where it is recorded in field InspectionEvent_FK.

That's how InspectionEvents, Jobs, and Mill Inspections are tied together.

The coils.............

Remembering that tblCoils is not and never will be an inventory-type table, there are many pieces of physical data that need to be recorded in tblCoils that wouldn't be included in our main coil inventory database anyway. Things like: "does the coil have a cardboard cylinder insert?" ; "does the coil have a peel coat applied?" ; etc. are not recorded anywhere in the inventory database but are absolutely critical pieces of data to collect to ascertain why we might be having issues with a particular coil or coils over time.

This data is best and most easily collected at the mill just during a mill inspection. In other words - the inspector knows nothing of which coil will be in the mill when he walks back to do an inspection. In other words, coil-related data relevant TO THE RUNNING OF THE MILL needs to be input at that stage not earlier when the coil is received. And as I have said before, we have partial coils that get used on several different jobs. Each of those jobs may have something unique about them that makes the event of the the coil being used more relevant than the coil itself.

Which means that data needs to be collected on frmInspectMill related to whatever coil number is associated with the material spooled on the mill - relevant to THAT inspection - not necessarily to the coil itself. Which is why the necessity of asking: "Are we looking at a coil that has never been entered or is this a partial that already exists in tblCoils."

Micron was concerned with adding data to "the many side" before adding it to "the one side" and that would be a concern if tblCoils was meant to be an inventory-style table but it isn't and never will be. We already have a robust system in place for that - but it is not linked to the inspection db at all - and never will be.

CoilNumber_PK is an autonumber so you don't have to insert it, Access will do that automatically.
I'll look more into your specific question re getting the value into the text box at " the bottom of the form ".

See if the attached db Coil Question 6 works for you when entering a made up coil number. The code for a new coil workds very well. Remember you have to start at the Main Menu and work through it. It does not act "right" if you just open frmInspectMill and try it from there.

...............when I added the coil and started to select values from your combo boxes then it worked and assigned the value to the textboxes for Coil_PK and InspectionEvent_PK.

the textbox for Inspection Event PK should be populated the moment that frmInspectMill is opened.

txtCoilNumber_PK would not be filled until either 1) the AfterUpdate event of txtCoilNumber finds and retrieves the CoilNumber PK ID from tblCoils and places it in txtCoilNumber_PK OR 2) the coil is not found and frmCoilStatus is opened to add a new coil, the coil is added; the button "Use This Coil" is clicked which closes frmCoilStatus, and returns to frmInspectMill (which is still open and never closed) and places the coil number in txtCoilNumber (assuming its not still there) and the PK ID of the coil in txtCoilNumber_PK.

Currently #1 does not work for me but #2 does work by using OpenArgs to open frmCoilStatus and returning the required values to frmInspectMill via form control references.

For #2, when a coil is not found and needs to be added, I've checked all of the underlying tables and the new and correct records are in place which tells me I can put my own value in txtCoilNumber_PK on frmInspectMill.

What I cannot figure out is in the case of #1 - when a coil already exists how to get and place the value of a coils PK ID from tblCoils and place that value in txtCoilnumber_PK.

Hope your quarantine is not driving you too nuts. It's a challenge here as well. I'm still at work but am very able to isolate and now the owner is having the office I share with two other inspectors professionally disinfected every 48 hours. This is nuts. Hopefully my little challenge is a distraction for you.

Thanks for your help!

Tim

 

[jdraw]

Thanks for the update and clarity. I'll take a look and maybe build a flow chart for reference.
You said

This data is best and most easily collected at the mill just during a mill inspection. In other words - the inspector knows nothing of which coil will be in the mill when he walks back to do an inspection. In other words, coil-related data relevant TO THE RUNNING OF THE MILL needs to be input at that stage not earlier when the coil is received.

"the inspector knows nothing of which coil will be in the mill when he walks back to do an inspection."
But he must know something about the Job. So perhaps there is another entity involving Job*Coil (perhaps not)?? But he is inspecting something and the coil is involved--he may not know it is a partial or new coil??
As for the self quarantine it's going ok. Our daughter brought us some groceries and I got my SSD to restore but have a lot of updates to apply). I'll worry about the Linux dual boot another time if needed.
Seems your boss has taken it all seriously, as we all should. Be careful you don't start smelling like disinfectant.
I'll look at the database you've supplied tonight - hopefully.
UPDATE:

I looked at the data base. A couple of comments:
- you should include Option Explicit as second line of all modules
-I recommend that you put comments in your code to assist you with what the purpose is and any related info. It will help you in 2 weeks/ 5 months when you have to adjust something. And for anyone who will maintain the code in future. Documentation isn't necessarily for you, it is for those who come after you.
- regarding the coil issues 1 and 2 related to Private Sub txtCoilNumber_AfterUpdate()

What I cannot figure out is in the case of #1 - when a coil already exists how to get and place the value of a coils PK ID from tblCoils and place that value in txtCoilnumber_PK.

CoilNumber is what the inspector adds to the form. This is a string not a number.
You search using coil number which is a string and return the CoilNumber_PK which is a number.

In this DLookup
'JDRAW change the constraint/where to use string - quotes on CoilNumber
' This does not error now.
txtCoilNumber_PK = DLookup("CoilNumber_PK", "tblCoils", "[CoilNumber] = '" & Me.txtCoilNumber & "'") 'this line errors 2471 not any more

I hope this resolves Issue #1, and you have #2 working ---so all seems ok at the moment.

Good luck.