Form Updating issue

[CyberJoe]

Background:
So I have been working on a tracking database for our backups. The original product was in excel. I created all my desired tables related them wrote some transformation code, imported the data into some temp tables then used my code to transpose from the temp tables into the desired tables and fields. The main table has some foreign keys fields that are blank/null and as such the related table did not have a record associated with this record which makes sense as this data was related to why you are removing the tape from storage thus those fields were left blank/null during the original transpose. So now the problem is that my storage form will not allow me edit/add to these records, that are missing the data because the foreign keys were blank. But the fields that had some data are completely editable records.

Also, while testing I noticed my new records now have the same issue. The intake form does not have data to enter into this related from so the new record in the main table has no relation to the table that tracks why an item is removed from storage. One solution would be to create a new blank record in the related table in order to generate a foreign key for the main log but that seems like bad practice. So trying to find ideas that will work with the from already created and associated queries.

So current form design:
In MS Access 2010 I have a form that is that use a combo box in form header that allows the users to select nonbarcoded media based on job and media name using a query I built, that is working. the form detail area has text boxes that populate the Job name, media name current storage location to verify that you do in fact have the desired record. Then there is one combo box to select the employee who is removing the tape and some other text box that allow you to pick date removed and text box that allows you to state the reason for removing the item.

So once I realized the issue is that the storage log does not have any association to these problem records my thought was ok then I need to build some code that will add a new record to the storage log and update the foreign field in the main log to associate everything.

I tried adding the code to the form combo box where I choose the employee which would check the to see if the foreign key value was null and should then create a new record in storage log and then update the other foreign key field in the main log with the primary key for the new record but it does not seem to work. So I trying to figure out where on my form and what event I should be focused on to create this new record and refresh the from.
I have tried several different events to know avail. I have not tried it on the main combo box in the form header because I don't want to create the record unless I actually am updating it. I am sure my issue is either the code I am using or the event I trying to tie it too. It just seemed to me to make sense with the combo box for the employee who is removing the items as that would be the first event.

the code I am using is:

Private Sub Combo19_AfterUpdate()
If IsNull(Me.Storage_ID) Then
Dim dbsStorageLog As DAO.Database
Dim dbsLog As DAO.Database
Dim rstLog As DAO.Recordset
Dim rstStorage As DAO.Recordset
Set dbsStorageLog = CurrentDb
Set dbsLog = CurrentDb
Set rstLog = dbsStorageLog.OpenRecordset("Log")
Set rstStorage = dbsStorageLog.OpenRecordset("Storage_Log")
rstStorage.AddNew
rstStorage!Removed_By_ID = Me.Removed_By_ID
rstLog!Storage_ID = rtsStorage!Storage_ID
rstStorage.Update
rstLog.Update
rstStorage.Close
rstLog.Close
dbsStorageLog.Close
dbsLog.Close
End If
Me.Requery
'me.refresh made no difference.
End Sub

I probably could have used fewer tables and ultimately avoid this issue but that's a mute point now as this is the last thing I have left to resolve. all my reports are functioning correctly now. I stumbled into this issue after I discovered an inconstant query that turned out to be an issue with an incorrect join type.

 

[CyberJoe]

After reading another post I am wondering if putting a subform in this form might be the ticket?

 

[Minty]

Yes - sounds a lot simpler. Also avoids most of the accidental new record problems, especially if you only let additions based on a command button to "Add a new record"

 

[CyberJoe]

Working on a subform I noticed my issues. I forgot because the keys are null I have to grab a record count after a field is filled in and assign the new record count value to the key field for autonumbering. Still working on the subform but now I at least I found my issue.

I had to do the same when I original transposed my imported data into the normalization tables, but that was a month or two back.

 

[CyberJoe]

OK so after playing around with things. I have managed to make things work from one form. I just need to look into making some controls visible or invisible based on processing sequence.

but this is my current code:

Option Compare Database

Private Sub Command56_Click()
'RecordSets
Dim dbsLog As DAO.Database 'primary log table
Set dbsLog = CurrentDb
Dim rstLog As DAO.Recordset

Dim dbsStorageLog As DAO.Database 'related child table
Set dbsStorageLog = CurrentDb
Dim rstStorage As DAO.Recordset
Set rstStorage = dbsStorageLog.OpenRecordset("Storage_Log")



'Variables
Dim varCount As Variant 'temp varable to determine the record count
Dim varCount1 As Variant 'temp count to used in record counting
Dim varFKey As Variant 'temp variable for determiening foreign key
Dim varPKey As Variant 'temp variable for detemrinei primary key
Dim varX As Variant 'temp variable
Dim varY As Variant ' Temp Variable
Dim strSQL As String ' variable for sql string
Dim varLogID As Integer ' variable for Log.Log_ID field
Dim varDate As Date ' var for grabing the system date
varDate = Now()

With Me
varLogID = !Log_ID 'grabs the current form record displayed primary key
MsgBox "Log_ID = " & varLogID
End With

With rstStorage
.AddNew
Me.Date_Removed_From_Storage.SetFocus
rstStorage!Date_Removed_From_Storage = varDate
rstStorage.Update
.MoveLast
varPKey = rstStorage!Storage_ID
varFKey = varPKey

strSQL = "SELECT * FROM Log Where [Log_ID] = " & varLogID
Set rstLog = dbsLog.OpenRecordset(strSQL)

With rstLog
rstLog.Edit
rstLog!Storage_ID = varFKey
rstLog.Update
End With
varY = rstLog!Storage_ID 'can remov3d once done testing
varX = rstStorage.RecordCount 'can remove once done testing
' MsgBox "Record count for Storage_log = " & varX
MsgBox "Storage_Log Primary key: " & varPKey
' MsgBox "Foriegn Key that Should be saved in Log is = " & varFKey
MsgBox "Log.LogID " & varLogID & " And log.storage_id = " & varY
End With
Me.Requery
Me.Repaint

With Me.RecordsetClone
.FindFirst "[Log_ID]=" & varLogID
If Not .NoMatch Then
If Me.Dirty Then
Me.Dirty = False
End If
Me.Bookmark = .Bookmark
End If
End With

rstStorage.Close
dbsStorageLog.Close
rstLog.Close
dbsLog.Close

End Sub

Private Sub Command74_Click()
Me.Requery 'when finished button is clicked it resets the form to start anew.
Me.Repaint
Me.Combo45 = Null 'clears the combobox previous selection as it is in the form header.
End Sub