Not In List - add new record to table from form and return to previous form (1 Viewer)

Zydeceltico

Registered User.
Local time
Today, 09:02
Joined
Dec 5, 2017
Messages
843
Hi All -

My database is about doing inspections of assembled pieces from formed coils of steel. It might take many coils of steel to complete a job. OR it might only take a partial coil - which is more likely the case - which necessarily means that the same coil could come up in many inspections across many jobs.

I have a table called tblCoils where all coil data is recorded. Primary key is auto as the coil number - while unique - can go through various processes that make it questionable to use the coil number as a primary key.

Coil numbers and related coil data are typically not entered into the inspection database until they are on the mill. We have a separate purchasing and inventory database that I have no part of that houses all of that data. The db I am currently writing about is a standalone with the sole function of recording inspection data although - clearly - it would be more efficient to have the two linked - that is not going to happen in the forseeable future - thus my question. No point in discussing that further.

So..........a coil is placed on a rolling mill and we begin part production. I'll do an inspection of the parts at this time. If we run that coil out, we'll put another coil up and continue production on the same job. This is where my question comes in.

I have a form called frmInspectMill which is used to record inspection data into tblInspectMill. One of the pieces of data that I collect on frmInspectMill is the coil number that is running. If the coil number I enter is "Not In List," a msgbox opens prompting as to whether or not I want to add the new coil. That code is:
Code:
Private Sub CoilNumber_FK_NotInList(NewData As String, Response As Integer)
On Error GoTo errline

Dim MsgBoxAnswer As Variant
Response = acDataErrContinue

'Request permission
MsgBoxAnswer = MsgBox("Do you want to add a new coil?", vbYesNo, "Add new coil?")

If MsgBoxAnswer = vbNo Then
    Me.CoilNumber_FK = Null
    DoCmd.GoToControl "CoilNumber_FK"
    Exit Sub
Else 'Permission granted to add new coil
    DoCmd.OpenForm ("frmCoilStatus")
    DoCmd.GoToRecord , , acNewRec
    Forms![frmCoilStatus]![CoilNumber_PK] = NewData
    Me.CoilNumber_FK = Null
    DoCmd.GoToControl "Gauge"
    
End If

errline:
    Exit Sub

End Sub

Clicking yes in the msgbox opens frmCoilStatus and places the value I just typed in the Coil control on frmInspectMill in the control named CoilNumber on frmCoilStatus. Then the cursor is automatically moved to the next control named Gauge. There are other txtbox controls on this form for more data but none of it is required.

One thing to note: on frmCoilStatus is a hidden control where CoilNumber_PK is held if the coil already exists in the table - - which in the case of adding a new record to the db - it doesn't.

On this form, frmCoilStatus, I have a Save and Close button. This code is attached to it:
Code:
Private Sub cmdSaveCoilStatus_Click()
On Error GoTo errline
DoCmd.RunCommand acCmdSaveRecord
On Error GoTo errline

Const conObjStateClosed = 0
Const conDesignView = 0
Dim IsFormLoaded As Boolean
Dim TempID As Integer

Me.Refresh
TempID = Me.CoilNumber_PK 'set CoilNumber_PK to Temp variable
' Check if the original data entry form with the list is open
' (this form might have been opened manually.)|

'''I THINK THE ISSUE HAS SOMETHING TO DO WITH NEW COIL NUMBER NOT YET BEING SAVED AS NEW RECORD IN TBLCOILS

If SysCmd(acSysCmdGetObjectState, acForm, "frmInspectMill") <> conObjStateClosed Then
    If Forms("frmInspectMill").CurrentView <> conDesignView Then
        IsFormLoaded = True
    End If
End If

If IsFormLoaded = True Then 'Check if the original data entry form with the list is open.
    'Refresh the list control so it now includes the newly added item
    Forms![frmInspectMill]![CoilNumber_FK].Requery
    Forms![frmInspectMill]![CoilNumber_FK] = TempID
    DoCmd.Close ' close the current form
Else
    DoCmd.Close 'if frmInspectMill is not loaded then also close this form
End If

errline:
    Exit Sub
End Sub


When I click my "Save and Close" button, nothing happens. No record is created. The form doesn't close. Nothing at all happens.

Any insight is greatly appreciated!

Something tells me that nothing is happening because maybe no record is - - at this point - - yet created in tblCoils. I'm not sure of the correct approach to accomplish this.

Thank you,

Tim
ps. I would attached the db but it is large and convoluted. If one really needs to see it to help, I will try paring it down tomorrow and posting.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:02
Joined
Oct 29, 2018
Messages
21,357
Yeah. I'd say having a sample db where we could step through the code to pinpoint the source of the problem would be nice. We just need the objects involved with the problem, you can delete the other stuff.
 

Zydeceltico

Registered User.
Local time
Today, 09:02
Joined
Dec 5, 2017
Messages
843
Yeah. I'd say having a sample db where we could step through the code to pinpoint the source of the problem would be nice. We just need the objects involved with the problem, you can delete the other stuff.
Hi DBGuy!

I've attached the stripped down version of the db. It will open to a "Main Menu." Click the "Inspections" button. When the Inspections form opens up, fill in the 4 required fields (it was going to be a pain to avoid this and still get to my question). It doesn't matter what you choose - then click the "Mill Inspection" button which opens an unbound form to make a choice. Choose either part - it doesn't matter. Click "OK."

Clicking "OK" opens frmInspectMill. Right now - and for ease of use while doing this testing - there are no required fields on this form. Going forward "Coil Number" will definitely be required and it will also have a formatting mask. Right now it does not. So.....type anything in the "Coil Number" field (e.g., "test coil 5"). Then either tab out or click a different field and you will be offered a msgBox asking if you want to add a new coil. Click "Yes" which opens frmCoilStatus.

When frmCoilStatus opens, notice that whatever you typed in frmInspectMill for Coil Number is passed to frmCoilStatus and the cursor has been moved to the Gauge control - which is so far acting as expected. (None of the fields on this field are currently required - going forward Coil Number and Gauge will be).

So - - -after entering the gauge I want to click the "Save and Close" button to:
1) save this new data to tblCoils;
2) close frmCoilStatus;
3) return to frmInspectMill having carried the new coil number ("test coil 5") back to frmInspectMill and placing it in the "Coil Number" combo box field.

But clicking "Save and Close" does nothing. At least not anything expected.

If I close frmCoilStatus using the "x" box and close frmInspectMill also using the "x" box and close frmInspections by clicking the "Close" button, I notice in the tables that:
1) there is a new entry for "test coil 5" in tblCoils;
2) there is a new entry in tblInspectMill but there is no data in the "CoilNumber" field.

Then I wonder if this has anything to do with the fact that - in tblCoils - CoilNumber is not the primary key. Said another way, I wonder if I am not seeing what I am expecting to see because either:
1) the new coil number is actually written to tblCoils while I am still in frmInspectMill OR
2) when I am in frmCoilStatus, the Save and Close does nothing because it wants to pass the key ID of the CoilNumber back to frmInspectMill instead of the literal coil number OR
3) ....something entirely different :)

I have been thinking about my reasoning that the CoilNumber is not the primary key in tblCoils and I am thinking that I may be wrong and that it would actually be OK to have the CoilNumber be the primary key also. There is no possible instance of two physical coils having the same coil number when related to the actual operation of the mill which is the singular focus of this db. If it would be easier to make the CoilNumber the primary key of tblCoils, I can see how that could make my life easier.

As always - thank you for your help with this.

Tim
 

Attachments

  • NotInListQuestion.zip
    196.4 KB · Views: 247
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 09:02
Joined
Jan 23, 2006
Messages
15,361
Tim,
Just opened you database. FYI.
I also see no Option Explicit and am getting some undefined messages.
 

Zydeceltico

Registered User.
Local time
Today, 09:02
Joined
Dec 5, 2017
Messages
843
Tim,
Just opened you database. FYI.
I also see no Option Explicit and am getting some undefined messages.
I never observed any "undefined messages." I did a compact/repair before compressing the db to post. Did I forget anything maitenance -wise before posting - just out of curiosity?
 

Zydeceltico

Registered User.
Local time
Today, 09:02
Joined
Dec 5, 2017
Messages
843
Tim,
Just opened you database. FYI.
I also see no Option Explicit and am getting some undefined messages.
I can't explain why there appear to be no Option Explicits in many of the form modules. Adding now - but I doubt that will make a difference to my bigger question. But thank you very much for pointing that out.
 

jdraw

Super Moderator
Staff member
Local time
Today, 09:02
Joined
Jan 23, 2006
Messages
15,361
There was a missing reference for Plum mdb or similar.

I don't know if you've seen this or if it's that relevant, but only takes a few minutes to review.
More for reference than your current issue

TimErroe2.PNG TimErrorOnStartup.PNG
 
Last edited:

Zydeceltico

Registered User.
Local time
Today, 09:02
Joined
Dec 5, 2017
Messages
843
There was a missing reference was for Plum mdb or similar.
That's a reference to a linked database at another plant that didn't strip while I was stripping the db down fo this post. Sorry about that. I'll try again.
 

jdraw

Super Moderator
Staff member
Local time
Today, 09:02
Joined
Jan 23, 2006
Messages
15,361
Tim,
Just opened your latest. Main menu - Click ->Opened the form->filled in the fields -->clicked mill Inspections, then got error
TimError3.PNG

OOOps--didn't see your Don't, until I was posting back.....!!!!
 

Zydeceltico

Registered User.
Local time
Today, 09:02
Joined
Dec 5, 2017
Messages
843
Here it is....and I saved it to the main computer screen instead of the big screen I work on - which I had forgotten to do which I also forgot s an issue when opening it elsewhere.
 

Attachments

  • NotInList2.zip
    163.4 KB · Views: 342

Zydeceltico

Registered User.
Local time
Today, 09:02
Joined
Dec 5, 2017
Messages
843
Tim,
Just opened your latest. Main menu - Click ->Opened the form->filled in the fields -->clicked mill Inspections, then got error
View attachment 79632

OOOps--didn't see your Don't, until I was posting back.....!!!!
I got the same. Should be fixed now.
 

Attachments

  • NotInList2.zip
    163.4 KB · Views: 339

jdraw

Super Moderator
Staff member
Local time
Today, 09:02
Joined
Jan 23, 2006
Messages
15,361
When I click Mill Inspection nothing opens, whatever I click I just get the "beep" from Access --basically frozen.??????
Had to shut down Access to get control?????
 

Zydeceltico

Registered User.
Local time
Today, 09:02
Joined
Dec 5, 2017
Messages
843
Merde........I should just post the entire db.........Taking parts out of it is..............I'm sorry.

I closed it and re-opened it here after cleaning up and it works just fine and there is something I am forgetting about posting zipped dbs to this site. Did a compact/repair before zipping. I'm open to suggestion because I am at a loss.
 

Zydeceltico

Registered User.
Local time
Today, 09:02
Joined
Dec 5, 2017
Messages
843
When I click Mill Inspection nothing opens, whatever I click I just get the "beep" from Access --basically frozen.??????
Had to shut down Access to get control?????
I found the culprit. I have two screens at work that work on. frmChooseComponent was opening on the second screen. And frmChooseComponent is Modal. If you don't have second screen you would see nothing but hear the Access beep. Try this - - I am so grateful for your patience.

Tim
 

Attachments

  • NotInList2.zip
    163.3 KB · Views: 355

jdraw

Super Moderator
Staff member
Local time
Today, 09:02
Joined
Jan 23, 2006
Messages
15,361
All is OK down to the Save and Close CoilStatus.

Some feedback:
I notice TempID is integer
Code:
TempID = Me.CoilNumber_PK 'set CoilNumber_PK to Temp variable

?me.CoilNumber_PK
test coil88 <---this is a string/text
 

Zydeceltico

Registered User.
Local time
Today, 09:02
Joined
Dec 5, 2017
Messages
843
All is OK down to the Save and Close CoilStatus.

Some feedback:
I notice TempID is integer
Code:
TempID = Me.CoilNumber_PK 'set CoilNumber_PK to Temp variable

?me.CoilNumber_PK
test coil88 <---this is a string/text
I know. I’m sure that’s an issue. Our real coil numbers are alphanumeric - thus strings.i believe I mentioned in an earlier post above that the PK for tblCoils is autonumber and there is another field where the actual alphanumeric coil number is stored. Is there a way to resolve this?

thanks!

Tim
 

jdraw

Super Moderator
Staff member
Local time
Today, 09:02
Joined
Jan 23, 2006
Messages
15,361
You can have an alphanumeric coil number and a coilID autonumber (2 separate fields). Lots of databases with say ProductID (PK /autonum) and ProductCode or ProductNum or ProductNo alphanumeric. Database uses the ProductID to uniquely identify each and every record in the table, while users, business processes refer to ProductCode/ProductNum/ProductNo etc.

I don't want to change things in your logic, and I'm not familiar with the details of what you are doing.
My first step for debugging would be to have error routine to highlight errors and line numbers-not just exit the sub etc.; also use some/several debug.print statements to see the actual logic flow; also breakpoints with stepping through code to check the detailed flow where you can also see the variables and values.

When you want to ensure a selected value actually updates or inserts a record in a table, you could use explicit SQL command. Moving off a form or record should save the record/changes, but if your logic is doing other things it may interfere with your intended update/insert. I'm not saying you should; just highlighting how I might "attack" the issue.
Another/additional option would be to draw a logic/flow diagram of what you want to happen and then make sure your code is doing what you intended. Those who just code without such a diagram can easily introduce variables or logic to "accommodate" something and get down a rabbit hole where plan B is to remove things until something works.
The old standby to resolving a complex issue is to break it into smaller pieces; solve the individual issue, then start to combine things building up to the more complex.
Anyway, enough soap box. I'm retired and you're working to solve a problem on a project with a deadline.
 

Zydeceltico

Registered User.
Local time
Today, 09:02
Joined
Dec 5, 2017
Messages
843
My first step for debugging would be to have error routine to highlight errors and line numbers-not just exit the sub etc.; also use some/several debug.print statements to see the actual logic flow; also breakpoints with stepping through code to check the detailed flow where you can also see the variables and values.

When you want to ensure a selected value actually updates or inserts a record in a table, you could use explicit SQL command. Moving off a form or record should save the record/changes, but if your logic is doing other things it may interfere with your intended update/insert. I'm not saying you should; just highlighting how I might "attack" the issue.
Another/additional option would be to draw a logic/flow diagram of what you want to happen and then make sure your code is doing what you intended. Those who just code without such a diagram can easily introduce variables or logic to "accommodate" something and get down a rabbit hole where plan B is to remove things until something works.
The old standby to resolving a complex issue is to break it into smaller pieces; solve the individual issue, then start to combine things building up to the more complex.

Hi JDraw - Your advice is well received and I've learned a lot this morning working through my code with your advice.

And I cannot find the culprit. :) so................

I'm just going to leave it the way I had it before that worked.

Before, when I typed in a CoilNumber that was "Not In List," I had it setup that I would get a msgBox asking if I want to add an item. I click yes and frmCoilStatus would open to the most recent record - not a new record. I used to have an "Add New Coil" button on frmCoilStatus which would change the state of frmCoilStatus to acNewRec. I would have to re-enter the coil number on this form instead of having it elegantly passed from Form1 to Form2 but then I could also add any other pertinent data, click "Save and Close" which would close frmCoilStatus saving the new coil number record which then would be recognized by the cbo on Form1. Everything worked very well.

Given deadlines - as you point out - I think I'll just go back to that approach even though I know what I was trying to do currently would be a lot more elegant - - - but time - - - time is truly a commodity. :)

Thank you,

Tim
 
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 09:02
Joined
Jan 23, 2006
Messages
15,361
Good stuff. You have your priorities straight. If I knew more of the intricacies I'd help trying to sort it out. But, if you have a working approach and a deadline--this isn't the time to start experimenting with something new.
Good luck.
 

Users who are viewing this thread

Top Bottom