SQL New Record Problem (1 Viewer)

[rgwfly]

I was hoping someone can help a noob. I have a main form and tab form. User enters basic information on the main form which passed to subform. After the rest of the information is entered I have a button which writes to table. the problem is that I need to go to the next record and set focus back on the main form. The code writes but the form seems to be frozen with the previous data and I cannot navigate the main form. Thanks for your help.

 Private Sub btnSave_Click()
Dim strInsert As String
strInsert = "INSERT INTO [Events] (Welder,Pass) VALUES ('" & Me.Welder & "','" & Me.Pass & "')"
  
 Debug.Print strInsert
CurrentDb.Execute strInsert, dbFailOnError
 DoCmd.RunCommand acCmdRecordsGoToNext
 Forms!frmEventDetails.SetFocus
Forms!frmEventDetails!cbWelder.SetFocus
 
End Sub

 

[sneuberg]

It's hard to see what's going on or what's supposed to be going on. Could you upload your database?

 

[missinglinq]

Using a SQL Statement to save your data would seem to indicate that you're using an Unbound Form...if this is true, then there is no 'next Record' to go to; Unbound Forms do not contain Records, merely data that can be written to Tables, as you appear to be doing!

Linq ;0)>

 

[rgwfly]

The form is bound to another table. I have two tables with similar attributes using two forms based on a testing procedure. I was trying to write the record to a master table so all the records would be in one place.

 

[sneuberg]

Ok so the only code relevant to the problem seems to be:

Private Sub btnSave_Click()

DoCmd.RunCommand acCmdRecordsGoToNext
Forms!frmEventDetails.SetFocus
Forms!frmEventDetails!cbWelder.SetFocus
 
End Sub

What's frmEventDetails and what is its relation to the form btnSave is on? Why are you setting focus on it. If it has controls it can't receive focus. And why are you setting focus on cbWelder? What form should go the next record?

This would be a lot faster if you uploaded the database.

 

[rgwfly]

OK I think I may need to rethink this. I attached the database I think.
I basically have two similar record entries. One record has a unique field (RTTest) so the entry cannot be duplicated. the other does not. I need to pull data from both tables.
Apologize this is my first real database trying sql more extensively.

 

[moke123]

i took a quick look at your db and noted extensive use of lookup fields in your tables. Please take a look at this...http://access.mvps.org/access/lookupfields.htm
lookup fields in tables often mask was is really being stored and will cause numerous problems going forward.

 

[sneuberg]

The immediate cause of the problem your are experiencing is this code

Private Sub Form_AfterUpdate()
On Error GoTo ErrorHandler
Me.Welder = Forms!frmEventDetails!cbWelder

ErrorHandler:
    Resume Next

    
End Sub

in the frmEventsQ form. This took me a while to figure out because the error handling in this masked the problem. I suggest you comment out any error handling until you get this working. Just ignoring an error, as this error handler does, is only appropriate for errors that are not really errors like cancel error you get when click a cancel button.

I don't have a recommendation on how to fix this problem as I can't figure out what you are trying to do. Normally key data is passed from a main form to the subform through the Link Fields but here the relationships are not clear. One thing that looks very wrong is the existence of tblEventsRT and tblEventsQ which appear to be nearly structurally identical. I think you need to work on your database structure before proceeding. I suggest watching this video on normalization, getting rid of the lookup fields, restructuring the database and posting a new thread with the database and a description of what you are trying to accomplish for comments.