Subform not saving automatically (1 Viewer)

[hprwdvd]

Hello!

I have been reading the many threads posted here and it has helped me resolve numerous problems in Access as a new user. Thank you very much!

I have one problem with subforms. I have one bound main form and two bound subforms: one subform is used to display existing records in datasheet view while another is used to enter new records in form view, but this latter subform for new records will not save automatically, nor will it save by using the me.refresh or me.dirty=true in the unload event of the subform control. The parent child relationship is intact. I can even see the primary key of the new row in the subform but it does not appear in the table that the subform is based on. This primary key adds by one automatically.

I have struggled and finally made work a combo box in the main form that finds existing records but also is able to add new records when the value entered is not on the list. Below is the code I used. I am not sure if this code is overriding Access's save ability of the subform?

Private Sub Combo316_AfterUpdate()
Dim n As String
gblvariable = cbobox
Me.Requery

n = DLookup("ID", "[Customers Extended]", "[Customer Name] = '" & Me!Combo316 & "'")

DoCmd.SearchForRecord , Customers, acFirst, "[ID] = " & Str(Nz(n, 0))
Forms![Case Details].Form1.Form.Recordset.AddNew

[Suffix].Locked = False
[First Name].Locked = False
[Last Name].Locked = False
[E-mail address].Locked = False
[mobile phone].Locked = False
[Home Phone].Locked = False
[Address].Locked = False
[street name].Locked = False
[street type].Locked = False
[apt no].Locked = False


End Sub


Private Sub Combo316_NotInList(NewData As String, Response As Integer)

Dim Db As DAO.Database
Dim Rs As DAO.Recordset

Dim F As String
Dim L As String

TempVars.Add "Space", InStrRev(NewData, " ")
F = Left(NewData, [TempVars]![Space] - 1)
L = Mid(NewData, [TempVars]![Space] + 1)
On Error Resume Next

intAnswer = msgbox("The customer " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Customer Care Database")

If intAnswer = vbYes Then

Set Db = CurrentDb
Set Rs = Db.OpenRecordset("Customers", dbOpenDynaset)
Rs.AddNew
Rs![Last Name] = L
Rs![First Name] = F
Rs.Update
Rs.Requery
DoCmd.Save
Response = acDataErrAdded

End If

End Sub

 

[RuralGuy]

Without first attempting to understand your code, are you aware that Access will *always* try and save a "Dirty" record whether on a MainForm or a SubForm when you try and leave the record. This includes closing a form. You would need to take extra steps to stop this native Access action.

 

[hprwdvd]

Thanks RuralGuy, yeah I did notice that and read that elsewhere, however, even after removing all the code from the unload, and exit events of the form and subform, including the subform control, it does not save it at all. Please Help!!

 

[RuralGuy]

You can use Debug.Prints or MsgBoxes to show where the code is going. Does the SubForm record actually get Dirty? Are you doing a Me.Undo somewhere in the SubForm?

 

[hprwdvd]

Where do I put the debug.print? anywhere in the vba?

The subform does not dirty because I am inputting data for 3 fields. Oddly enough, if I change the data entry to No, you can see the the record I've just inputted when pressing Next on the navigation buttons, but then once you close and reopen, it all disappears and it's not in the table at all.

 

[hprwdvd]

btw, the main form is able to save data on it just fine

 

[hprwdvd]

I just created a simple form with a main form and subform, the main form is able to look up the correct records in the subform with parent/child links, but again the subform data is not saving!

 

[hprwdvd]

Is there something I'm missing with subforms, besides parent/child links? I learned Access as of 2 months ago...

 

[RuralGuy]

Where do I put the debug.print? anywhere in the vba?

The subform does not dirty because I am inputting data for 3 fields. Oddly enough, if I change the data entry to No, you can see the the record I've just inputted when pressing Next on the navigation buttons, but then once you close and reopen, it all disappears and it's not in the table at all.

DataEntry *only* shows NEW records that have not been saved yet. Once saved, it will not show while DataEntry property is set to YES.

 

[RuralGuy]

I just created a simple form with a main form and subform, the main form is able to look up the correct records in the subform with parent/child links, but again the subform data is not saving!

Is the RecordSource of the SubForm editable or is it Read Only? Any chance you could post that simple example so we can look at it? Remove any sensitive data of course but make sure it exhibits the issue before posting.

 

[hprwdvd]

Thanks RuralGuy for offering your assistance at this holiday time!! I have removed pretty much all the tables/forms/reports except for "Form3", which contains the subform, and the table "Cases", which is where the data in the subform should be going. There's a few others that are not important. Thank you for your help!!

 

[Galaxiom]

You can use Debug.Prints or MsgBoxes to show where the code is going.

It is generally simpler and more incisive to use the Locals Window in the VBA editor. It shows the values of the variables and objects when the execution is in Break.

Check out the Watches Window too. It can be configured to cause a Break when particular conditions are met. Then the values associated with the condition can be read from the Locals.

 

[RuralGuy]

Instead of the code you have in the AfterUpdate event of the ComboBox, use the cbo wizard to create another cbo and select option #3 and see how that works. You might also want to review this link: http://access.mvps.org/access/lookupfields.htm

 

[RuralGuy]

I'm sorry. Did I lose you there?

 

[hprwdvd]

Hi RuralGuy,

Sorry you didn't lose me, it's just that I can't change it to the button that comes with the macro because i need to put in the after update and not in list code events (copied in my first post). The combo box needs to not only find records but add in new records on the Same form (feedback from my boss). Otherwise the not-in-list function on the side panel under properties enables a form to pop up to add in new records, but my boss is tired of forms popping up. Hence the very long-winded code you saw earlier.

Also, I just noticed that in my simple form, the data in the subform is saved when I don't use the combo box at the top. But when I do use the combo box at the top to select a record, the data entered in the subform is not saved. I wonder if I need to put a bookmark after the searchforrecord code for it to recognize it? I'm not sure why this is happening

 

[hprwdvd]

Btw, I just used the wizard for the search record function, and it worked for adding in the subform data! I wonder what's wrong with my vba for searchforrecord? I guess either I turn all my long code into macro form or figure out a way to do the searchforrecord macro in vba form. I'm at home right now and my online version of access keeps crashing each time i go into vba view, so I will try it tomorrow. Thank you for following up

 

[RuralGuy]

Let's see if I have this right. The ComboBox on the MainForm is to look up records on the MainForm and if it finds it, move to that record on the main form and have the SubForm sync to that new record as well. If no record is found on the MainForm you need to create a new record on the MainForm *and* the SubForm and keep the two forms in sync. Does that sound correct?

 

[Cronk]

hprwdvd

You could have made life a lot simpler for yourself and anyone looking at your db, by better naming of fields.

The PK for Customers is ID => should be CustomerID

The PK for Cases is ID, FK is Customer => should be PK CaseID, FK CustomerID

Also name controls explicitly when referring to them in code eg it should be cboCustomer, not Combo316

Also better practice is no spaces in field names.

Use the combo to show case records for an existing customer. To add a new case, just type on the new record line.

Don't use the combo to add a new customer. Use an "Add Customer" button to open another form to enter a new record and then requery the combo and form.

Incidentally, another good tip is to set variable declarations by choosing in the VBA window, Tools | Options.

And another thing, you will have a problem when you go to look up a Customer who has an apostrophe in their name eg O'Toole. Use chr(34) for string delimiters in such a case.

 

[hprwdvd]

How can I update the combobox with the macro from Access for SearchforRecord with a selection made from another unbound combobox? The unbound combobox is used to select the customer name (first name and last name put together in a query) while the SearchforRecord combobox is used to select that particular record in the main form.

I find that I am able to add subform records if I find the record in the main form using the SearchForRecord macro that is selected from the wizard for comboboxes.

I have tried:
Me!Combo337 = Me!Combo316.Column(1)
Me!Combo337.setfocus

where Combo337 is my searchforrecord macro button and Combo316 is the customer name button, but it contains the name in Column(0) and the ID in Column(1).

But this does not trigger the SearchForRecord macro and putting the macro in the OnFocus event of the SearchForRecord button seems to make it go in an endless loop.

Sorry for the misnaming, I will change next time around.

Thank you for your help,

 

[RuralGuy]

Did I get the issue correct in my post #17?