Custom record navigation with "new record" option

[Seth Schrock]

I have a form with two subforms. The main form contains customer information. The first subform contains all the company information related to the customer. The second subform just lists the company names and is used as a navigation form of sorts for the first subform. Clicking on a record in the navigation subform changes the record being viewed on the first subform. As part of the recordset of the navigation subform, I have a UNION query to add the "New Record" option so that I can just click on the "New" record to add a new company. My problem is that when I first create a customer record, my "New Record" option isn't shown. I have to close out of the main form, reopen it and then navigate to the customer's record before I have the "New Record" option available.

I have tried all sorts of refresh/requery of the navigation subform both at the subform control level and inside the form (Me.Subform.Requery and Me.Subform.Form.Requery) and it doesn't make a difference.

Any ideas?

[twinnyfo]

Seth,

If I understand you correctly, when you open this main form, your navigation subform has the "New Customer" option. But, when you click that option, and the new record shows up, the "New Customer" option disappears in the subform? Thus, you are unable to add new sequential customers?

Does it make a difference if you add a new customer and then select a different customer? That is, once it's gone, it's gone--until you close the form and re-open it?

Could you share the code that fire when you select a new customer? That might help us....

[Seth Schrock]

It is the "New Company" option that doesn't appear, not "New Customer". Customers being part of the main form and the related companies part of the subforms. When I open the customers form, the code that runs just sends it to a new record automatically. What I would expect is that as soon as I save the new customer record, I would have the option to click the "New Company" option in the navigation subform so that I can enter a company for that new customer. I can just start typing in the first subform (the one with all the data), but once I have filled in that record, the navigation subform isn't updated, so I don't have the option to go to a new record to enter a second company. I have to close out of the customer main form and then reopen it, navigate to the customer that I had just entered, and then I have the "New Company" option.

[twinnyfo]

You mention that you've tried many refresh/requery methods.

Have you tried, after updating the customer, refreshing the cusotmer data and then refershing the company subform?

OR.... Simply clicking a button on the customer subform that takes you to a new company?

I'd have to see it in action to really understand what is going on. But, i think your bottom line is that you want to be able to add a new company at the time of adding a new customer, correct?

[Seth Schrock]

I haven't tried refreshing the customer form via code. I did do a refresh using the button in the ribbon bar and that didn't do anything.

And yes, your understanding of the "bottom line" is correct.

[zmbd]

Seth, on the new record, you will have to call the related subform to requery after the record has been update.

The refresh doesn't pull new records in, just changes to the current records.

Refresh or requery data
Refreshing records only updates the data that already exists in your datasheet or form. It does not reorder records, display new records, or remove deleted records and records that no longer meet specified criteria. To perform those tasks, you can requery the records. You can requery data manually, by using a macro, or by using code.

If I have the relationships right we have
Parent
Child1 - call it brother
Child2 - call it sister

So, in the afterupdate event of frm_brother

If Me.Dirty Then Me.Dirty = False
Me.Parent!frm_sister.Requery

then comboxes on sister:
Me.frm_Parent!frm_Sister.Form!CBO_OnSister.Requery

do this quite often... never have figured out why the comboboxes have to be forced separately, one would thin that the subform.requery would enough for all of the controls to requery too...

I need a nap

[Seth Schrock]

You do have the relationships correct. So what I'm currently doing is entering a parent record. When I click on the next tab (where brother and sister are), I save the parent record so that it generates the PK value (linked to Microsoft Azure currently, but had the same problem when I was linked to Access) and then requery sister. The problem is that sister still doesn't show my "New Record" text from the UNION query. I have tried both Me.frm_sister.Requery and Me.frm_sister.Form.Requery. Neither work.

I already have the After Update event on brother so that when I add a new record, it updates sister to have the complete list.

[zmbd]

Did you happen to try the If Me.Dirty Then Me.Dirty = Fals trick in frm_brother? That was the key in one of my databases. For some reason, the record was being held up, I'm guessing record locking, and this did something to force the final record save.

[Seth Schrock]

I use the DoCmd.RunCommand acCmdSaveRecord command without the IF to save the record when I do make changes. However, there would not have been any changes made to frm_brother, only to frm_Parent. So your code would return false.

[zmbd]

Most likely then a red-herring; would you humor me and stick it in? At worst nothing happens and to a chemist that information is just as valuable as having the expected happen too. The line can always be deleted out.

[Seth Schrock]

I added it as well as a Debug.Print line to let me know if it ran and it never did.

[Rabbit]

What's the SQL for the data in your navigation subform?

[Seth Schrock]

SELECT AcctInfoID_pk
, CustID_fk
, CompanyName
, CompanyID_pk 
FROM tblAcctInfo INNER JOIN tblCompany 
ON tblAcctInfo.CompanyID_fk = tblCompany.CompanyID_pk 
WHERE CompanyID_fk <> 1 
UNION 
SELECT 0
, 1
, '<<New>>' As CompanyName
, null 
FROM tblAcctInfo 
ORDER BY CompanyName DESC

[Rabbit]

Which field is the link between the main form and the navigation sub form?

[Seth Schrock]

CustID_fk. Now that you mention that, I see why I am changing the recordsource of the navigation subform in the parent form's OnCurrent event to make the UNION part of the query have the correct value in the second field. I'll have to try to make that change when I save the new record in main form.

[Seth Schrock]

Thats what it was. Thanks Rabbit for pointing me in the correct direction.

[Rabbit]

I reset the answer because it wasn't really a full answer.

What you can use for the query is something like this:

SELECT key, value
FROM Table1 
 
UNION 
 
SELECT key, 'New Record' AS value
FROM Table1

With that query, you don't have to mess with the record source of the subform. And then you can pop in a Me.subform.requery in the after insert event.

[Seth Schrock]

I have to have the primary key field (AcctInfoID_pk) so that I can use the form to navigate the other subform, I need the foreign key field to bind the subform to the parent form and I need the name field to show, so I don't think that your solution would change anything.

[Rabbit]

It's just an example. What you want to do is create a "New Company" record for each distinct value of the key that links the main form to the subform. This allows you to bypass the need to modify the record source everytime.

SELECT AcctInfoID_pk 
, CustID_fk 
, CompanyName 
, CompanyID_pk  
FROM tblAcctInfo INNER JOIN tblCompany  
ON tblAcctInfo.CompanyID_fk = tblCompany.CompanyID_pk  
WHERE CompanyID_fk <> 1  
 
UNION  
 
SELECT 0 
, CustID_fk
, '<<New>>' As CompanyName 
, null  
FROM tblAcctInfo   INNER JOIN tblCompany  
ON tblAcctInfo.CompanyID_fk = tblCompany.CompanyID_pk  
WHERE CompanyID_fk <> 1  
 
ORDER BY CompanyName DESC 

[Seth Schrock]

Ah... I follow now. You are a genius Rabbit! This is the true solution to my problem.

[Rabbit]

Glad I could help