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

[Micron]

Then you have at least 2 entities - Coils and Processing. A coil is a unique thing, even if it completely resembles another in all physical characteristics - same as people when they are twins. Serial numbers would be a unique index, all necessary data pertaining to the coil in other fields.

Processing (or whatever you want to call it) is an instance where the coil is fed into the mill. If that is the 2nd time, the coil ID is pulled from data because this eliminates typos when it gets reloaded and you now have 2 (or more) processing records for that serial. IMO the time to enter the coil as an entity is when it is received, but if that's not feasible there is still a better approach IMO. It goes against normal practice to be creating records on the 'many' side of a relationship and worrying about the 'one' side later. Even if I didn't want to enforce logging a coil when received, I'd make it requisite to enter the 'one' side value first before allowing the creation of records on the many side at the moment it's in the mill and you've stepped up to the podium. In such a design, you can then do away with free form serial entry when you might be dealing with a serial that is already in a record. That's where the FAYT or listbox suggestion was coming from.

So in one simple scenario,
you step up to the podium, enter the serial in an unbound textbox and press 'find'
not found? Prompt to add
add if Yes, then requery the main and subform (which you don't have but would show multiple mill loads if you did)
or it was found, so load the form(s) with records for that serial.
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.

 

[Zydeceltico]

So.....I added an unbound textbox called txtCoilNumber and modified and added this code to frmInspectMill:

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
            Debug.Print " Coil " & tempCoilNo & "  has been used previously. This is a partial coil"
        Else  'brand new coil so add it to tblCoils
              'CurrentDb.Execute    Debug.Print "Insert into tblCoils (CoilNumber_pk,CoilNumber) Values (" _
               & DMax("CoilNumber_PK", "tblCoils") + 1 & ",'" & tempCoilNo & "')"   , dbFailOnError
            DoCmd.Close acForm, Me.Name
'reopen the form showing the record with new Coil number
'this command could go  into a different event to conform to your logic???
       DoCmd.OpenForm "frmInspectMill", , , "CoilNumber ='" & tempCoilNo & "'"
       End If
End Sub

In order to completely replicate opening frmInspectMill as intended, the typical order of operations is:
1) from the Main menu click "Inspections" which opens frmInspectionEvent;
2) pick a job number (which I do update weekly behind the scenes) and pick an operator and inspector (doesn't matter who);
3) click "Mill Inspection" which opens frmChooseComponent (because different assembly parts are rolled on different mills);
4) choosing a component part and clicking "OK" opens frmInspectMill;
5) enter the coil number of whatever coil is on the mill in txtCoilNumber (labelled "Coil Number."

So I just tried it and something is squeehonk.

I entered what I know to be a coil number in unbound txtCoilNumber that does not exist ("Z98989") and tabbed out of txtCoilNumber. The first thing that happened was that frmInspectMill closed and reopened to a blank form without any type of messaging occurring with all fields blank. I then opened tblInspectMill and notice that no coil ID was recorded in the CoilNumber_PK field of this table. (I should probably change that field name to "_FK" but right now it is consistent with the code and the conversation.) The I opened tblCoils and notice that new entry was recorded there either.

Then I closed everything and started over from the main menu. This time when I got to frmInspectMill I tried three things.
1) I physically clicked inside of txtCoilNumber to set the cursor and focus there. I typed nothing. Then I clicked and set focus in a different control and received no message saying a coil number needed to be entered.
2) set the cursor back in to txtCoilNumber and tabbed out - nothing.
3) then I typed a single letter in txtCoilNumber and clicked out - the form closed and reopened blank;

Then I tried uncommenting your CurrentDB.execute line to see if that would tell me anything but then I received a compile error: expected expression.

 

[jdraw]

Guys,
I think we all recognize some confusion -mainly because of our lack of familiarity with the process(es) involved. 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 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.

 

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

 

[Micron]

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​