How to make bound cbo be blank Form open

[Zydeceltico]

OR
just have a find form. Enter the serial,
if not found, prompt to add, if yes, open the form(s) blank but enter that serial in the form control
if found, open the form loaded with that record.
Click Add New, unhide the search form, rinse and repeat

I think I'd prefer everything on the one form, but doing so would make it more complicated for you (maybe) in terms of reloading forms. A simple requery won't work as it will take you to the first record when what you'd want is to use the Bookmark property or FindRecord method.

Every word of this quote is exactly the issue I'm facing. I'd prefer to have it all on one form but it clearly makes it far too compllicated for my simple mind with little time.

On the other hand - - I *could* (and I believe I already know how to ) strip my existing "frmCoilStatus" form down to just the search cbo at the top of it and accomplish what you mention in your quote above ("....OR just have a find form........") I hadn't thought of that. I think I could make that work if I can't figure out JDraw's idea - which I do actually understand when I read the code but for some reason is still giving me problems.

 

[Zydeceltico]

The more I read or conjure up the more I see that a coil seems to be an attribute of Job. So somehow there is a need to know which Job or Jobs used coil XXX for some sort of statistics on Inspections.

Yes - that is correct. Currently Jobs and Coils are related ultimately through tblInspectionEvent which acts a junction table of sorts between tblJobs (always) and the various types of inspections (mill inspections sometimes).

Maybe what is not clear is that a given coil can very often be an attribute of many jobs?

Also - the MAJOR part of my logic regarding coils is all about issues with coils that cause line stops and failures to occur. The attributes of a coil that are recorded in tblCoils serve only one purpose and that is to be able to look at patterns of failure over time and across commonalities (e.g., over time I should be able to run a report asking: do all oil canning events occur with coils from a single supplier or if there is no observable pattern within suppliers over time should we look more at our internal production processes.

Does that help at all? But yes- the only time I care about a coil number - in this database whose single purpose is to record inspections - it is always related tblJobs latently because tblJobs carries the product info which dictates part/product number which dictates which mill and coil width. They are all related. And - for a given job - if it is big enough - we will run a single product but that mill run might mean that we go through many coils which all may come from different suppliers and may have been received yesterday or ten years ago - and any of these could be partials.

I am spending some time (just got off Skype) to add some conditional Debug.Prints to each event. I've got to find a way to do this via code. I'm trying to get a picture of your logic flow.

and I am really grateful because on top of helping me get this done you both are greatly adding to my education so that hopefully some day soon I can actually "give" to this forum instead of continually receiving. Thanks!

.....and JDraw - - I would love to know how to do this in VBA.

Thanks guys for "sticking with me."

 

[Deleted member 147267]

It looks to me that it is working exactly as coded and as you describe and as I commented on:

If user doesn't like to see form open and close again,

Since that is not my code, I will bow out because I think you're in good hands and will get this solved. I don't seem to be adding much in the effort to arrive at a solution anyway.

 

[Zydeceltico]

The more I read or conjure up the more I see that a coil seems to be an attribute of Job. So somehow there is a need to know which Job or Jobs used coil XXX for some sort of statistics on Inspections.

I don't know if it will help or not but attached is the db with a button added to the Main Menu called Example Query. Maybe seeing an example of what I'll be looking for in a report will help clarify?

Here's the SQL for it:

SELECT tblinspectionevent.datetime,
       [jobnumber] & [task] & [resource] AS Job,
       Operator.[firstname]              AS Operator,
       tblparts.parttype,
       tblworkstations.workstation,
       tblcoils.coilnumber,
       tblcoils.supplier,
       tblinspectionnotes.inspectionnotes
FROM   lutblpersonnel AS Operator
       INNER JOIN (tblworkstations
                   INNER JOIN ((tbljobs
                                INNER JOIN (tblinspectionevent
                                            INNER JOIN (tblcoils
                                                        INNER JOIN (tblparts
                                            INNER JOIN tblinspectmill
                                                    ON tblparts.part_id =
       tblinspectmill.parttype_fk)
       ON tblcoils.coilnumber_pk =
       tblinspectmill.[coilnumber_pk])
       ON tblinspectionevent.inspectionevent_pk =
       tblinspectmill.inspectionevent_fk)
       ON tbljobs.job_id = tblinspectionevent.job_fk)
       INNER JOIN tblinspectionnotes
       ON tblinspectionevent.inspectionevent_pk =
       tblinspectionnotes.inspectionevent_fk)
       ON tblworkstations.workstation_id = tblparts.workstation_fk)
               ON Operator.personnel_pk = tblinspectionevent.operator_fk
ORDER  BY tblinspectionevent.datetime;

 

[jdraw]

Micron,
Your comments and thoughts are always welcome.
Tim,
I am attaching a snip that I took after adding some conditional debug.prints. I opened frmInspectionEvent and you can see in the immediate window I have captured the sequence of events that have occurred. You can also see the problem with not using Option Explicit in every module.
I have manually entered the conditional debug.prints. I haven't done all events/functions but the concept is working generally.
My view at this point is that the what we are trying to design has not yet been clearly identified. We are working against a variety of interpretations of the process and within thee constraints of some existing code that may or may not be aligned with the ultimate requirement.

Note: The undefined variable is during the Form_Load event of form frmChooseComponent.

Note: I am working with a copy of the database you attached to a post early this morning Mar 28/20.
It came as Coil Question.zip

 

[Zydeceltico]

Micron,
Your comments and thoughts are always welcome.
Tim,
I am attaching a snip that I took after adding some conditional debug.prints. I opened frmInspectionEvent and you can see in the immediate window I have captured the sequence of events that have occurred. You can also see the problem with not using Option Explicit in every module.
I have manually entered the conditional debug.prints. I haven't done all events/functions but the concept is working generally.
My view at this point is that the what we are trying to design has not yet been clearly identified. We are working against a variety of interpretations of the process and within thee constraints of some existing code that may or may not be aligned with the ultimate requirement.

Note: The undefined variable is during the Form_Load event of form frmChooseComponent.

Note: I am working with a copy of the database you attached to a post early this morning Mar 28/20.
It came as Coil Question.zip

I just posted Coil Question 4.zip which is updated to include your code modified to reflect control names and also includes a query that may help make more sense of what I am ultimately trying to accomplish.

When looking at your screenshot of the immediate window - and if I am accurately understanding what I am looking at - what is happening is that I am trying (with limited knowledge) to deliver the id for that inspection event from frmInspectionEvent to frmInspectMill to establish the new record in tblInspectionEvent with the soon-to-be new record in tblInspectMill.

 

[Zydeceltico]

Note: The undefined variable is during the Form_Load event of form frmChooseComponent.

intInspID is a global variable. It worked fine until I started messing with trying to change that cbo to have a blank field.

 

[jdraw]

The screenshot showing the immediate window is showing each of the Events that the code has entered processed and exited up until the point of failure/success. It shows exactly what "Access" has done/logic flow.
I have not looked at latest zip files/databases. I could look at those, but we're still trying to understand design of tables and relationships and the logic involved with "mounting a coil". Reports can wait until structure is resolved.

 

[Zydeceltico]

The screenshot showing the immediate window is showing each of the Events that the code has entered processed and exited up until the point of failure/success. It shows exactly what "Access" has done/logic flow.
I have not looked at latest zip files/databases. I could look at those, but we're still trying to understand design of tables and relationships and the logic involved with "mounting a coil". Reports can wait until structure is resolved.

Understood. I was just thinking that "purpose" might help explain structure.

 

[Zydeceltico]

Note: The undefined variable is during the Form_Load event of form frmChooseComponent.

Not that I'm shocked - but my coding appears sloppy - lol.

I just made txtboxes for InspectionEvent_PK on frmInspectionEvent and InspectionEvent_FK on frmInspectMill visible and commented out the line of code in the On Load event of frmChooseComponent so I can see if the id gets passed from frmInspectionEvent to frmInspectMill - which it does - which means that the On Load line of code on frmChooseComponent is unnecessary - and doing nothing -and can be deleted.

 

[jdraw]

Ok. I'm going to drop the stuff I was working on and look at Coil Question 3.zip post #42

 

[jdraw]

This is a snip of the afterupdate event of the unbound textbox. I put in a breakpoint to step through the logic and made the Debug.print active. You can see that The code is trying to insert a new record into tblCoils with the oil number entered into the textbox.

Insert into tblCoils (CoilNumber_pk,CoilNumber) Values (50,'Z98989')

The problem is that other related records in other tables must exist, and to reopen the frmInspectMill there are coded events that have specific logic such that the insert can not work. There has to be a clear statement of what should happen, and ensure that that logic is possible. I don't like to remove your existing event logic just to make this work. But that's where things are at the moment as I try testing.

 

[Zydeceltico]

I'm getting closer.

I took the "Debug.Print" out of the CurrentDb.Execute statement and it worked - kind of.

Then I entered a non-existent coil number in txtCoilNumber. When I moved the focus off of txtCoilNumber the frmInspectMill closed and reopened but it did not have the coil number placed in txtCoilNumber - however - it DID record the coil number as a new record in tblCoils. That is progress.

Now I need to figure out how to get the new coil number to "stay" in txtCoilNumber without re-entering it so the rest of the inspection can be completed.

And now to re-read many of the above posts because I think you two likely already addressed this.

 

[jdraw]

I commented out the load and current events and activated the Currentdb.execute code and got new record into tblCoils as per attached.
But there are field values missiing that would/could be filled if the necessary logic was used.

 

[Zydeceltico]

View attachment 80267
I don't like to remove your existing event logic just to make this work. But that's where things are at the moment as I try testing.

Actually - I think you should remove existing logic to see what needs to be removed to get it to work. That would be quite telling I think.

 

[Zydeceltico]

I commented out the load and current events and activated the Currentdb.execute code and got new record into tblCoils as per attached.View attachment 80268
But there are field alues missiing that would/could be filled if the necessary logic was used.

I did the same. ...and have the same question. Because in fact, there are several fields that are most easily recorded at the mill.

What do you think necessary logic is?

I am hoping there is a way to open something similar to frmCoilStatus to fill in several more required fields at the time of inspection.

That is how I had it operating when coil number was going into a combo box by using the List Items Edit Form.

 

[jdraw]

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?

Did you recently change the data type of CoilNumber_PK? I had to include the DMax in stuff I as doing because it wasn't autonumber?

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​

 

[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.