Solved Not In List event to open Add New Coil Form (1 Viewer)

[Zydeceltico]

Hi all -

My current conundrum involves........where to begin. Same place as always I suppose

I do inspections of rolled steel coming off of rolling mills. The raw material is coiled steel. It may take more than one coil of steel to finish a job.

I've posted about this before recently but find myself needing to revisit the question.

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

I have frmInspectMill which I open when I am walking up to the mill. On that form is a cbo control (cboCoilNumber currently bound to tblCoils). There are two relevant fields in tblCoils: 1) CoilNumber_PK which is autonumber and 2) CoilNumber which is an alphanumeric string, e.g., P32106 or R32990).

Currently cboCoilNumber has two columns with the first one being the bound one (CoilNumber_PK, column width set to zero) and the second one, CoilNumber, column width set to "1." The typical setup.

There are LOTS of coil records already in tblCoils.

So.....when I open frmInspectMill, cboCoilNumber automatically shows the first coil in tblCoils which is totally erroneous.

What I would like to do is open frmInspectMill with cboCoilNumber empty and begin typing the coil number of the coil that is on the mill and have one of two things happen as the coil number is being typed either 1) the coil number autofills because it is able to read tblCoils to see if a record already exists or 2) the coil number is found to be "Not In List" at which point the user is prompted as to if he would like to "Add a new coil?" and frmCoils is opened as a data entry form to add a new record.

I would love some general direction as to how I am able to accomplish this. I am wondering if changing cboCoilNumber to an unbound control would be a better idea.

I've attached a "dummy" db that almost gets the job done. You'll need to click the "Inspections" button and work through a couple of required cbo's and then click "Mill Inspection" - you can pick anything - doesn't matter at this point. frmInspectMill will open. You'll notice that I've been able to get cboCoilNumber to be empty upon opening frmInspectMill. But try typing a new coil number in there....it will take any strong but try R77777 for example. The message box opens and asks "Yes" or "No"." If I click yes it should open frmCoils but it does not and only expands the cbo list.

What *should* happen upon clicking "Yes" is that frmCoils should open with the coil number I tried typing into frmInspectMill placed into txtCoilNumber and the cursor should move to "Gauge" and after I enter gauge I should be able to click "Save and Close" and have frmCoils close (with a new record saved in tblCoils) and return to the-still-open frmInspectMill with the new coil number properly placed in cboCoilNumber.

Thank you - as always - for your patience and indulgence.....my brain hurts

Tim

I'm pretty sure I am to close to the forest to see the trees with this and it is probably a typo in the VBA - - - hoping for less blind eyes.

Tim.

 

[Pat Hartman]

So.....when I open frmInspectMill, cboCoilNumber automatically shows the first coil in tblCoils which is totally erroneous.

Not in my version. It is null. When you start typing in it, it scrolls down the list - PS, always sort this list alphabetically. Yours is not sorted and that may cause confusion.

To have the NotInList event work, create a form for the user to enter the coil data and put it into the "list items edit form" property. I don't know if you have to set the "limit to list" property to yes or to no. Start with yes, and if that doesn't work, switch to no. I never use this method or I would be able to give more details. Personally, I find that allowing users to enter values into lists on the fly just leads to typos so in 99.9% of cases, I would make them enter a new value in the coil table before trying to examine it. But, the flexibility is there if you need/want these to be entered on the fly.

 

[Zydeceltico]

Not in my version. It is null. When you start typing in it, it scrolls down the list - PS, always sort this list alphabetically. Yours is not sorted and that may cause confusion.

To have the NotInList event work, create a form for the user to enter the coil data and put it into the "list items edit form" property. I don't know if you have to set the "limit to list" property to yes or to no. Start with yes, and if that doesn't work, switch to no. I never use this method or I would be able to give more details. Personally, I find that allowing users to enter values into lists on the fly just leads to typos so in 99.9% of cases, I would make them enter a new value in the coil table before trying to examine it. But, the flexibility is there if you need/want these to be entered on the fly.

Pat -

That's exactly the advice I was looking for and it makes sense. And....there is time in the real world to do it.

Inspector walks up to mill. Sees coil tag (identifier) and checks to see if coil is already in db or not and - if not - enters new coil - then proceeds with mill inspection.

I'll try to work through this - because that is a much better approach. Thank you.

What is the best way to have the user enter a piece of string data and have it checked for an existing record against an existing table and then coming to a decision point as to whether or not to add a new record?

(sorry for typos - making pizza from scratch for my kids atm and don't want to miss out on anything - anywhere.

 

[Pat Hartman]

Use dcount() or dlookup()