Solved acNewRec on Mainform problem (1 Viewer)

[foshizzle]

I have a mainform with a continuous subform. The main form is used for data entry using bound controls. I have a save button on the main form with the following code. When I try to clear the main form for a new record, it says 'You can't go to the specified record. and points to the line DoCmd.GoToRecord , , acNewRec.

The goal is to have the main form clear all controls and prepare for new record after any button is pressed and that action has completed. i.e., if you click edit on one if the items in the continous form, the data moves to the main form. Clicking the Delete button does delete the data, but the next record remains on the main form controls instead of a clear for for new records.

DB attached for reference. Thanks!

Private Sub cmdSave_Click()

 'test required fields
 If IsNull(Me.txtAirlineCode.Value) _
 Or IsNull(Me.txtAirlineCompany.Value) _
 Or IsNull(Me.cboProviderName.Value) _
 Or IsNull(Me.cboAirlineStatus.Value) Then
    MsgBox "Please fill in all required fields."
 Else

  MsgBox "Record saved."

  'Requery forms
 Me.Requery
 Me.frmAirlineMasterSub.Form.Requery
 
 ' Prepare form for new record input
 DoCmd.GoToRecord , , acNewRec

 End If
 
End Sub

 

[June7]

No need for tblFuelProviders in either form RecordSource.

You are emulating a Split form. Did you explore this intrinsic form model?

You are using Image controls as command buttons. As already demonstrated in your other thread, behaviors are not the same. Although appears not to be cause of issue in this case.

Controls on main form are bound to table fields. If you 'clear' them then you remove or undo data entry. Commit user input to table then move to new record. Simply moving to new record row will commit current record. Then should requery the subform.

DoCmd.GoToRecord , , acNewRec
Me.frmAirlineMasterSub.Requery

IMO, lookup fields in table is bad idea.

 

[foshizzle]

No need for tblFuelProviders in either form RecordSource.

You are emulating a Split form. Did you explore this intrinsic form model?

You are using Image controls as command buttons. As already demonstrated in your other thread, behaviors are not the same. Although appears not to be cause of issue in this case.

Controls on main form are bound to table fields. If you 'clear' them then you remove or undo data entry. Commit user input to table then move to new record. Simply moving to new record row will commit current record. Then should requery the subform.

DoCmd.GoToRecord , , acNewRec
Me.frmAirlineMasterSub.Requery

IMO, lookup fields in table is bad idea.

I'm not sure what you mean by 'intrinsic' model but thank you, this worked. I was not aware there was a difference between command buttons vs image control; I figured both were controlled by a macro or VBA? What if the code was the same?

Regarding the lookup field in a table; are you referring to my combobox? I chose a table because I figured the actual table would always have the updated record (airline) in this case, regardless of what is in the current table/query. i.e., if its a blank table with no fields, there would be no airline in this to choose from. Perhaps I am missing something.. Perhaps you mean making a separate query for this alone? Still learning..

 

[foshizzle]

Update on above; I saw the comments on my other post regarding the command button and that it would normally take focus. Understood!!

 

[June7]

By 'intrinsic' model I mean the form is a choice in the Create Form ribbon group.

A button is just that - a button that runs some code. An Image control can be bound to field therefore it is a data control.

I mean the field would just be a normal field, not formatted in table for combobox or listbox, especially if a key is saved instead of descriptive text. Review http://access.mvps.org/Access/lookupfields.htm. You still need the lookup table but just build combobox or listbox on form.

 

[foshizzle]

By 'intrinsic' model I mean the form is a choice in the Create Form ribbon group.

A button is just that - a button that runs some code. An Image control can be bound to field therefore it is a data control.

I mean the field would just be a normal field, not formatted in table for combobox or listbox, especially if a key is saved instead of descriptive text. Review http://access.mvps.org/Access/lookupfields.htm. You still need the lookup table but just build combobox or listbox on form.

thanks i will definitely look into this.

 

[June7]

To be fair, here is counterargument https://improvingsoftware.com/2009/10/02/blog-response-lookup-fields-in-access-are-evil/

 

[foshizzle]

thanks i will definitely look into this.

I had setup a similar DB setup a while back which also used subforms because i didnt want the user to be able to edit the subform directly. This is why I have the edit button by each row/record on the subform which moves that record to the main form for editing.

 

[foshizzle]

No need for tblFuelProviders in either form RecordSource.

You are emulating a Split form. Did you explore this intrinsic form model?

You are using Image controls as command buttons. As already demonstrated in your other thread, behaviors are not the same. Although appears not to be cause of issue in this case.

Controls on main form are bound to table fields. If you 'clear' them then you remove or undo data entry. Commit user input to table then move to new record. Simply moving to new record row will commit current record. Then should requery the subform.

DoCmd.GoToRecord , , acNewRec
Me.frmAirlineMasterSub.Requery

IMO, lookup fields in table is bad idea.

How would this work for the Delete button? If I apply the same logic by deleting the current record then moving to a new record, the form is showing the next record instead of a new one..

 On Error Resume Next

 RunCommand acCmdDeleteRecord

 DoCmd.GoToRecord , , acNewRec

 Me.frmAirlineMasterSub.Requery

 

[June7]

Requery or Refresh sets focus to first record. Perhaps Requery before GoToRecord.

 

[foshizzle]

Requery or Refresh sets focus to first record. Perhaps Requery before GoToRecord.

That didnt work. Actually the main form always seems to be going to record #11 after every delete. Which doesnt make sense even if it was sorted some weird way that i can tell. you should be able to use the existing database above for testing if you want to see it.

 

[June7]

Correction to my earlier statement: including tblFuelProviders in RecordSource of subform is reasonable in order to show provider name since there is no combobox.

Try:

DoCmd.GoToRecord acDataForm, "frmAirlineMaster", acNewRec

 

[foshizzle]

Correction to my earlier statement: including tblFuelProviders in RecordSource of subform is reasonable in order to show provider name since there is no combobox.

Try:

DoCmd.GoToRecord acDataForm, "frmAirlineMaster", acNewRec

Thanks! That did it. So even though Im calling this command from the main form itself, I still have to reference it?

 

[Marshall Brooks]

Try:

DoCmd.GoToRecord acDataForm, "frmAirlineMaster", acNewRec

Not sure how the forum feels about resurrecting an old thread, but this worked for me also.

I had an intermittent error where DoCmd.GoToRecord , , acNewRecord would sometimes fail and stay on the current record.

I added code to check the primary key before and after the command and compare them and report if they were the same, which prevented overwriting data, but it was unpredictable when the command would or would not work.

Explicitly calling out the form eliminated the error, even though the form was the currently open form (and should have been the same as the default object).