Subform not saving automatically

[hprwdvd]

Hi RuralGuy,

You have it correct. That is what the long code in the first post does. If the name entered is not on the list, the NotOnList code will be triggered, followed by the AfterUpdate code. It works except I can' save data entered in the subform, which makes me think the DLookup way of finding records in the afterupdate event does not work. However, I have also tried this recordset.clone and the bookmark method, it does the same thing but no new records in the subform are saved.

 

[hprwdvd]

btw, Merry Christmas!!

 

[hprwdvd]

If a combobox value is given by another control, how do you get the effect as clicking on that combobox and selecting the value that way? I have tried .setfocus but it does trigger the SearchforRecord macro which uses .Activecontrol

 

[hprwdvd]

And sorry just a correction, the "buttons" I refered to in post #19 are comboboxes. So Combo335 and Combo337 are comboboxes, not buttons.

 

[RuralGuy]

btw, Merry Christmas!!

Right back at you. If you will use the cbo wizard to create a cbo and select option #3, that cbo control can then be modified to do exactly what you want.

 

[RuralGuy]

Give me a minute or two and I'll modify your sample as I have been suggesting. You don't get Option #3 with a dynamic query. It would need to be a static query.

 

[RuralGuy]

Okay...maybe a few hours. Man am I rusty or what? See if this does what you want.

 

[hprwdvd]

RURALGUY, THIS LOOKS GRREEEAAATTT!!!! I am going to put it into my actual database and see. THANK YOU FOR YOUR HELP!!!!

 

[RuralGuy]

You're very welcome. Glad we could help.

 

[hprwdvd]

Hi RuralGuy, I've tried it in my master database and it works great. I had to change the cycle of the main form from Current Record to All Records. I have one question, is there a way to undo the newly added subform record upon closing the form? The Customer Name can be saved in the Customers table, (because there was a prompt anyway), but can the same be done for the subform?

I tried the following on the unload event of the subform:

If msgbox("Save the case information?", vbQuestion + vbYesNo, "Customer Care") = vbNo Then
Me.Undo

Else
Me.Dirty = False

End If

However it is still saved.

 

[hprwdvd]

Hi RuralGuy, sorry just noticed something else. I assigned default values to some fields in the subform (in my master database) via the property settings panel on the side when the form is in design view. However, none of these work for a new record added to a new customer name. Could you amend the code so that the default values in the subform would be picked up as well? Funny enough, the default values in the subform are picked up when i select an existing customer from the drop down menu.

 

[hprwdvd]

I have one default value that is a function for a DueDate field:
=dhAddWorkDaysA(5,[OpenedDate])

where [OpenedDate] is a field in the subform with a default value of today's date.

Otherwise, I suppose we could manually put the names of the fields into the INSERT INTO statement and assign the default values directly.

Your help would be greatly appreciated once again!

 

[hprwdvd]

Okay I answered my second question:

DateDue = dhAddWorkDaysA(5, Now())
MySQL = "Insert Into Cases([Customer], [First Response Due]) Values(""" & NewID & """, """ & DateDue & """)"

And I put the rest of the default values into the Default Value section of the Table itself in design view, not only in Form view of the form.

But is there a way to undo the subform record addition?

 

[RuralGuy]

The SubForm *will* save the current record if you attempt to move off of the record for any reason, including closing the form. The place to catch it is in the BeforeUpdate event of the SubForm.

 

[hprwdvd]

Hi RuralGuy,

I will try that. I have another question: Because my subform is in form view and not datasheet view (due to the many fields and ease of use), is there a way to get the subform to open on a new record? For example, after adding in a new customer name, it lands on the just added record which is a new record which is good. However, if you pick a name from the dropdown list, it will open the previous record for that customer with no place to add new data.

I have tried:
If Me!Form1.Form!Category = "" Or IsNull(Me!Form1.Form!Category) = False Then
Forms![Case Details].Form1.Form.Recordset.AddNew
msgbox ("Added new")
End If

The MsgBox tells me that it actually did it, and the subform record at that point is blank, however, afterwards the subform reverts back to a previous record with all the fields filled. Do you know how I can rectify this?

 

[hprwdvd]

I stuck that code in the AfterUpdate event, after:

If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark

which you have provided.

 

[RuralGuy]

I'm sorry but I have to go run an errand for a couple of hours. I'll check in when I get back.

 

[hprwdvd]

Thanks Rural Guy

 

[hprwdvd]

Hi Rural Guy, here is what is doing the trick right now:

Combo316_AfterUpdate_Exit:
Form1.SetFocus
If Me!Form1.Form!Category <> "" Or IsNull(Me!Form1.Form!Category) = False Then
DoCmd.RunCommand acCmdRecordsGoToNew
Else
End If
Exit Sub

I built on the afterupdate code you provided. It' s not ideal because the old record flashes before the new one is inserted, but it's the best that I can do.

 

[RuralGuy]

Okay, I'm back. Let's see if we can figure out what is going on here.