Zydeceltico
Registered User.
- Local time
- Today, 15:48
- 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:
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:
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.
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.