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

[jdraw]

Tim,
Like Micron said "I'm starting to lose track of what you have going on ".
I have sent this link before, but I'm sending it again for you to review to see if it relevant to your current issue re "not in list".

 

[Zydeceltico]

I envision that I enter a value, the list starts filtering (assuming there is one - I'm starting to lose track of what you have going on) and when I click or tab out of that field, DLookup looks for the value entered and since it won't be found (if there is list filtering going on and there are no records visible) the fact that the lookup returns no record is what triggers the prompt.

Research 'find as you type'
To learn how to prompt, research the message box function. The DLookup result (0) would be the trigger. The answer is passed back to the function and you do what you need to do based on the returned value of the function.

Thanks!

 

[Zydeceltico]

Tim,
Like Micron said "I'm starting to lose track of what you have going on ".
I have sent this link before, but I'm sending it again for you to review to see if it relevant to your current issue re "not in list".

HI JDraw - Just so you know - I have made great use of the info from that link you've sent before! Thanks - That is exactly how I have had the control in question setup. And it works great - for me - but the other guys found it confusing because they'll be in the middle of a new inspection and there is already a coil number showing in the combo box - the first record in tblCoils. They want to see the coil number control be blank so they don't accidentally forget to put the coil number of the coil on the mill in that combo box. In other words - what COULD happen is that they both do a bunch of mill inspections and forget to put the new coil number in and we wind up with innumerable inspections all showing the same coil which happens to be the very first record in tblCoils.

That's why I'm trying to figure out how to do a table-level record search for a coil number from a textbox (which will be blank) whereas, since the current cboCoilNumber has a SELECT query as its rowsource there apparently will always be a record showing in it - the first record of tblCoils.

 

[jdraw]

Tim,

Can you restate the steps for this inspection process? It seems that an inspector would have to confirm the coil number being inspected. If there is a list of coils, then when a coil is inspected, it would no longer be available. I'm just trying to see how the tblCoils is involved. I worked in an area with a paint line so understand the overall process sort of. There are a number of coils; 1 is selected and mounted; it goes through the line; it is probably inspected at 1 or more stations as it goes through the line and a final inspection; that coil is dismounted and the process repeats.

Could you have an unbound text box that the inspector has to fill (your blank coil number)? Then compare whatever they have entered with another value (you may have said this in one of the posts, but I don't remember). I'm sure we can work this out once we understand the process and the "pieces involved". Sorry if it' just me who hasn't kept up with this.

 

[Zydeceltico]

Can you restate the steps for this inspection process? It seems that an inspector would have to confirm the coil number being inspected. If there is a list of coils, then when a coil is inspected, it would no longer be available. I'm just trying to see how the tblCoils is involved.

I'd be happy too.

The front end of our inspection db is going to ultimately reside on a rugged tablet so that we can spend more time on the floor and less time at our desks. Which means that we're walking from one mill to the next and there is no book anywhere on the floor that lists all of the coils.

I'll walk up to any given mill that is running to do a parts inspection. On the mill operator's workstation will be two pieces of paper: 1) the worklist for the job and 2) a small card called a coil tag that resides in an envelope attached to each coil when it is first received into the plant. Receiving and material certification data would be recorded in our company-wide system at this point - - which is not and never will be connected to the QC database in anyway.

The moment of walking up to the mill could easily be the very first time an inspector ever sees that coil tag.

tblCoils: most of the challenges/issues we come across are material issues. And we suspect by supplier and also by other extraneous variables that are all important for deducing an answer at a later date. In other words, tblCoils IS NOT meant to be an inventory table. That said, any coil that is on a mill should have it's coil number noted during a mill inspection in case in able to deduce if an oil canning issue is manifesting from a single supplier or not (we have many suppliers).

Also, it happens that a single coil may or mat be 100% depleted in the course of a single job - which means that I could do a record a coil number today for a mill inspection on a small job - - and the coil is not depleted so it is returned to the coil bay. That same coil may be used again at some unknown later date and I would do another inspection and need to enter the coil number again - which would already exist in tblCoils along with all of the other data intrinsic to the coil.

I worked in an area with a paint line so understand the overall process sort of. There are a number of coils; 1 is selected and mounted; it goes through the line; it is probably inspected at 1 or more stations as it goes through the line and a final inspection; that coil is dismounted and the process repeats.

Could you have an unbound text box that the inspector has to fill (your blank coil number)? Then compare whatever they have entered with another value.

That's what I want to do. I just don't know the mechanics of how to do it. Basically, have a textbox that the inspector enters the coil number in on frmInspectMill and when clicking or tabbing out the AfterUpdate event gets fired which takes the value just typed and and searches for it in tblCoils. If it finds a match to what was typed then the typed coil number remains in the text box and we move on but if the search does not find the typed value a message box popsup to say "Coil not found. Would you like to enter a new coil in tblCoils?" If "yes" then it opens frmCoilStats something like what happens with the "List Items Edit Form" property of a combo box to add a new coil to tblCoils. THEN - once the new coil is added to tblCoils, click "Save and Close" and return to frmInspectMill with the typed value of the new coil number still in place.

Works great with combo boxes - - except the first record always shows - at least for me and the guys. Like I said - they want to see it blank as a reminder to enter the coil number of the coil mounted on the mill.

Does that make it clearer?

I'm sure we can work this out once we understand the process and the "pieces involved". Sorry if it' just me who hasn't kept up with this.

HA! You are not alone my friend! I can't keep up with this myself - - - lol

 

[Zydeceltico]

I'm just trying to see how the tblCoils is involved. I worked in an area with a

So in a nutshell - tblCoils stores data regarding steel supplier, coating supplier (if coated), perforated or not, etc. etc.

The idea is that over time we can run a report on "paint scratches" for instance (which we record during inspections) and by linking to tblCoils ascertain whether paint scratches observed on any given mill run or even type of product are related to a single paint/coating supplier or if we should be looking elsewhere.

 

[jdraw]

Thanks for the update.
"a message box popsup to say "Coil not found. Would you like to enter a new coil in tblCoils?" If "yes" then it opens frmCoilStats something like what happens with the "List Items Edit Form" property of a combo box to add a new coil to tblCoils." Where does the new coil number come from? How exactly does the inspector/person get and enter this new coil number?

 

[Zydeceltico]

Where does the new coil number come from?

It is generated by the purchasing department when the coil is ordered from a supplier. The purchasing department creates a coil tag to be placed on the physical coil when it is received. The coil number is stored - at this point - in the company's large ERP system - -which again - is in no way and will never be connected to the QC database that I am developing. In other words - at this point - the QC database does not know the coil exists.

How exactly does the inspector/person get and enter this new coil number?

The inspector typically does not know the coil number until walking up to the mill.

Here's a walkthrough.

That's where the inspector get's the coil number for a mill inspection - not from anywhere else.
And that job might be done and we put up a completely different kind of coil - returning the unused partial coil to the coil bay. And at some point in the future we might put that same coil back on the mill and use the rest of it for a different job.

 

[jdraw]

Looks familiar.
I'm going to describe a different set up just for context. There was a company that build sails for boats.
Sails had different materials, some had more than 1 type of material, some had color pieces/inserts etc.
Sails were built by the job. But a job could use different rolls of cloth. The cloth was purchased in rolls from a variety of suppliers. The question the poster had was - How to identify the cloth available for new jobs given that any job could require more than 1 cloth type or color and unused cloth would go back to inventory and available for another job. His related requirement was How much cloth is available on each roll since a given job may only use part of a roll.
The adjusted model may apply. I think you would need to relate Job and Coil to identify issues with a Coil and/or Jobs that used that Coil.

So, you can have a job that uses part of Coil234. Then return the unused remainder of Coil234 to inventory/available coils. Coil234 will be in your table "jobsUsedCoil" or similar name (your Coil Bay). If I understand the current requirement, if a job needs a new coil to be mounted - it could be a new coil, or Coil234. This requires that the coil Tag stays with (or can be identified) the partial coil (in order for the person loading the partial coil knows/can identify the coil number).

Tim,
(Note I was trying to work with an early version of your database which may have changed recently??)
I added a textbox text312 to frmInspectMill and added some code, which
will accept a CoilNumber entered by the Inspector, and see if it exists in tblCoils already, if so it carries on, if not it is intended to add the new CoilNumber then reopen the form to that coil record.
But, you have other code in the form load event etc that I haven't deciphered that interferes with my form reopen--so this is not a final solution. You also have some linkage to form frmInspectionEvent.
I don't know the intricacies of your logic. In your relationships window in the version of your database I have tblCoils is not related to anything. You may be able to weave this into your logic.

Private Sub Text312_AfterUpdate()    'new textbox for CoilNumber
          Dim tempCoilNo As String
          'new coil number check
10        If Len(Me.Text312 & "") = 0 Then
20            MsgBox "You must enter a Coil Number", vbInfo
30            Me.Text312.SetFocus
40        End If
          'check if coil is partial/has been used before
50        tempCoilNo = Me.Text312
60        If DCount("CoilNumber", "tblCoils", "CoilNumber ='" & tempCoilNo & "'") > 0 Then 'coil exists
70            Debug.Print " Coil " & tempCoilNo & "  has been used previously. This is a partial coil"
80        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
90            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???
100       DoCmd.OpenForm "frmInpectMill", , , "CoilNumber ='" & tempCoilNo & "'"
110       End If      
End Sub

 

[Micron]

If Len(Me.Text312 & "") = 0 Then 20 MsgBox "You must enter a Coil Number", vbInfo

I don't agree with that. If you remove the current value and leave the field you will get prompted. I'd only do the check if there is a value after the update, otherwise, do nothing.

70 Debug.Print " Coil " & tempCoilNo & " has been used previously.

did you intend a message box? No one will see a debug.print

If user doesn't like to see form open and close again, just requery the form if the control isn't null and after the serial is added go to the added record
If Nz(Me.Text312,"")="" then exit sub
Me.Requery
DoCmd.FindRecord me.Text312, , , , , , True

because that control is not bound, correct, thus it will not get updated by the requery?

 

[jdraw]

Thanks for the comments.
I used a textbox because of this part of Tim's response:
They want to see the coil number control be blank so they don't accidentally forget to put the coil number of the coil on the mill.....
This is in the AfterUpdate of the textbox.
The Debug.print was for me and the testing I was doing to ensure both the True and False sides of the IF were working. A MsgBox would be used to inform the worker depending on the requirement. In my interpretation and approach - if this was a partial coil that had been used in part in another job, then that CoilNumber would already exist in tblCoils and that part of the form would be filled in. The idea was to keep the worker process constant (at least this small part) - whether the Coil is new or partially used, the worker enters the Coil tag number into the textbox. New Coil numbers are added automatically to tblCoils via the Insert SQL and the form opened to this new coil number, if an old coil the Coil number is found via the DCount and the form opens with that coil number.
That's the approach I envisioned and was trying to communicate, BUT I do not yet understand the related forms/tables/processes. And it may be totally out of context since this is only one small piece of a larger set of inter-related processes.
The point of all of this is to provide some ideas for consideration.

 

[Zydeceltico]

I added a textbox text312 to frmInspectMill .................

Hi JDraw and Micron - A rare Saturday for me to be at work - - - - and I am really grateful for your input because it's going to be a long ten hour Saturday. - meaning lots of time to work on this.

I'm going to work through your guidance. One question before I start: the textbox you added (text312) - is it bound or unbound? Does it have a rowsource?

I'll work through what you've provided to integrate with the other requisite logic of frmInspectMill and see how far I can get and then try to post the db back up.

...and JDraw - reading your final post at 7:39 - that's exactly what I'm after.

Thank you.

 

[jdraw]

text312 is unbound.
I have comments in the code, and a summary in the post and response to Micron's comments.
What I don't have/know is the logic/flow of your forms and events. Also, I downloaded an older database you had posted, so may not be working with current info. And I plead guilty to NOT having followed the various threads on this topic. It appears that the relationships in my copy of the database are not current or incomplete -tblCoils is not related to any other table. So it must be event logic that is demanding entries in other tables when I attempt to add the worker supplied Coil Number to tblCoils.
The interaction of other forms with frmInspectMill is where my approach has conflict.
A diagram showing the process/logic flow would be helpful for communications.
Because of Covid-19 I'm in personal isolation, so do have some time for forums and TV.

 

[Zydeceltico]

They want to see the coil number control be blank so they don't accidentally forget to put the coil number of the coil on the mill.....

Exactly - the most important thing.

In my interpretation and approach - if this was a partial coil that had been used in part in another job, then that CoilNumber would already exist in tblCoils and that part of the form would be filled in. The idea was to keep the worker process constant (at least this small part) - whether the Coil is new or partially used, the worker enters the Coil tag number into the textbox. New Coil numbers are added automatically to tblCoils via the Insert SQL and the form opened to this new coil number, if an old coil the Coil number is found via the DCount and the form opens with that coil number.

Yep.

I do not yet understand the related forms/tables/processes.

If you are wondering about the OnLoad event of frmInspectMill (this code):

Private Sub Form_Load()
  Dim rs As DAO.Recordset
  If Not Trim(Me.OpenArgs & " ") = "" Then
    'See if record exists
    Set rs = Me.Recordset
    'MsgBox Me.OpenArgs
    rs.FindFirst "InspectionEvent_FK = " & CLng(Me.OpenArgs)
    If rs.NoMatch Then  'it does not exist so you need to create it
      DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
      Me.InspectionEvent_FK = Me.OpenArgs
    End If
  End If

End Sub

Since I have multiple TYPES of inspections (extra fabricating, painting, packaging for shipping, etc.) that are basically unrelated to each other, I have a table for each type of inspection. Since I want - at some point - to run a report on all inspections (all types) on a given job, I have a table called tblInspectionEvent that acts kind of like an index or table of contents so to speak which is the umbrella for all inspections. This is where date, operator, inspector, and job number are recorded.

On the other hand - if you're talking about the On Load event of frmCoilStatus - where I enable and/or disable various controls on frmCoilStatus - that can all go away for the time being as it is all contingent on how I end up resolving this current question.

Here's a screenshot of current relationships as I see them.

It may help to recognize that I currently recognize tblCoils as kind of a lookup table primarily although I didn't follow my own naming convention with it.

I've attached the entire db as it currently exists. Maybe that will help? Maybe it will only confuse more.

 

[Zydeceltico]

Because of Covid-19 I'm in personal isolation, so do have some time for forums and TV.

I WISH I was currently in quarantine. Not to sidebar or derail but the company just released a company wide email giving guidance that may allow for it - - but I can post about THAT in the dedicated corner of the forum.

 

[jdraw]

Do you follow the logic in the text312_AfterUpdate code I provided?
When the worker types a TabNumber into text312, if it is a new coil number, I'd add a new record in tblCoil, but it seems I should also add a new record in tblInspectionEvent. Is that correct?
If it is an exiting coil number, do you add a new record for this Coil, this Job in tblInspectionEvent ??
Is there a reason that you did not use an autonumber on tblCoils?
What exactly is tblCoilChanges and where does it fit?

 

[Zydeceltico]

Do you follow the logic in the text312_AfterUpdate code I provided?

Yes. very well. Thank you.

When the worker types a TabNumber into text312, if it is a new coil number, I'd add a new record in tblCoil, but it seems I should also add a new record in tblInspectionEvent. Is that correct?

No. The coil number ID needs to be added to tblInspectMill because that is where all of the specific and unique data is recorded for unique and specific mill inspections. tblInspectionEvent is only an umbrella table for recording Date, JobNumber (which houses a host of data), inspector and operator names.

If it is an exiting coil number, do you add a new record for this Coil, this Job in tblInspectionEvent ??

. No. The coil number ID (which should be autonumber - and is now) - not the coil number itself - should be recorded in tblInspectMill.

Is there a reason that you did not use an autonumber on tblCoils?

Long story - and I apologize for THAT confusion - late yesterday I was playing with making the coil number itself the primary key as they are all unique and I forgot to change back to having an autonumber ID field (CoilNumber_PK). I corrected that in the attached db. I am sorry.

What exactly is tblCoilChanges and where does it fit?

You can just ignore tblCoilChanges at the moment. Primarily because I don't think it is going to exist later. For normalizing and general real world workflow reasons, I believe it makes more sense to collect that data on frmLineStop and record it in tblLineStop.

Most important to bear in mind is that even though I have a top menu level means of adding a new coil - that very, very rarely happens. In other words, every single coil in tblCoils is a partial or depleted coil because they are only entered when they actually on the mill. Remeber - this db is not connected to our inventory database.

and JDraw - - - (and Micron) - - -- thank you for all of this help! I hope it provides some interesting (if not relaxing) distraction.

It sure is helping me enormously. It would take me a long time and a ton of research to come up with the code above on my own.

 

[Zydeceltico]

If it is an exiting coil number, do you add a new record for this Coil, this Job in tblInspectionEvent ??

just to clarify - - -No, I do not add a new record for this Coil - directly into tblInspectionEvent. The coil is added to tblInspectMill which could be metaphorically thought of as "sub-table" of tblInspectionEvent along with all of the other types of inspections. an example of why this is so is, for instance, a painting inspection has nothing to do with the coil - nor does fabrication or shipping/packaging for that matter - but there is a separate inspection event recorded for an instance of inspecting painting or fabricating.

But yes - - a new record is added for this Job in tblInspectionEvent.

Think of it as tblInspectionEvent recording "instances" of various types of inspections. Job_ID is always recorded in tblInspectionEvent because the job contains all of the relevant and static product info.

As far as inspections and coils are concerned, Coil Number (the coil number ID more specifically) is only recorded in tblInspectMill (always) and tblLineStop (if need be).

 

[Micron]

Where this is at is pretty much exactly what I said to do in post 13 where I said to use an unbound textbox for this. I have to wonder why it took so many posts to get to what is really a simple task and solution. Maybe that's because we didn't know what kind of form this was; e.g. single, form/subform or what, or the goal morphed from one thing to another.

I question the relationships (solely based on the comments because I have not looked at any file) where you have a table for coils yet you only want to add a new coil number to an inspection table. As the seer says "I see more trouble in your future.".

 

[Zydeceltico]

Where this is at is pretty much exactly what I said to do in post 13 where I said to use an unbound textbox for this. I have to wonder why it took so many posts to get to what is really a simple task and solution.

Probably because I am thick-headed and don't really know what I am doing.

I question the relationships (solely based on the comments because I have not looked at any file) where you have a table for coils yet you only want to add a new coil number to an inspection table. As the seer says "I see more trouble in your future.".

Thus multiple posts......... I believe this keeps coming up because one - anyone - would assume that a table for coils would be an inventory-type table where coils are entered when they are received but that is not ever how this works in this database for its intended purpose. The only time a coil is added is when it is physically on the mill which could be hours, days, months, or even years after it was received. And the real bugger is that we don't always finish a coil when it is on the mill meaning it could go on and off the mill several times - and as much as years apart.