Subform going back to first record upon refresh

[jammin140900]

Hi All,

I have a subform in datasheet view with two combo boxes. The first represents a Company Name, the second contains the contact person/s for a company. I have currently set up so that when entering a company name, the second combo box will requery and only display contact people from the selected company.

To do this, I have the following query that sits behind the second combo box

SELECT qryfrmAuditorSelection.AuditorID, qryfrmAuditorSelection.AuditorSign, qryfrmAuditorSelection.AuditorSubID
FROM qryfrmAuditorSelection
WHERE (((qryfrmAuditorSelection.AuditorID)=[Forms]![frmAuditing]![frmAuditingSub].[Form]![Combo44]))
ORDER BY qryfrmAuditorSelection.AuditorSign;

To complete the process, I have a

Private Sub Combo44_AfterUpdate()
Me.Combo46.Requery
End Sub

to requery the data in preparation for the second combo box selection. That works fine.

Problem occurs when you try entering the next company in, the subform goes back to the first record instead of staying with the current record. Could anyone please help me with code to keep the subform at the current record after the requery? I've looked through a few threads on similar things and tried a few things but I cannot get it to work.

Thank you in anticipation!

 

[MarkK]

I would try...

Private Sub cbo44_AfterUpdate()
[COLOR="Green"]  'directly re-assign the rowsource property of the combo
  'and I expect the control will implicitly requery without requerying the form[/COLOR]  
  me.cbo46.rowsource = _
    "SELECT AuditorID, AuditorSign, AuditorSubID " & _
    "FROM qryfrmAuditorSelection " & _
    "WHERE AuditorID = " & me.cbo44 & " " & _
    "ORDER BY AuditorSign;"
End Sub

I think this is also easier code to understand and maintain, since what's happening here is very explicit.
Cheers

 

[jammin140900]

Lagbolt,

You are an absolute LEGEND mate! Thank you very much... It worked. I've spent hours and hours trying different things to fix it and was unsuccessful. Thanks once again, really appreciate your help with this.

Cheers,
Mark

 

[jammin140900]

Missed seeing that one...

Hi Lagbolt,

I've been using the database today and noticed something. The code you gave me works a treat when you are currently in the form and adding data to it. I've checked the tables and it stores the data in the tables immediately.

When you exit and reopen the form though, Auditor's names disappear. Only the first record name remains and if you click the drop down box in the next line item for Auditor's name, it tries to look at the Company Name from the first record and gives you the list of auditors related to the first company and not the company selected within the line item.

I've noticed that if you reselect the company for a line item, then the Auditor name requeries correctly and you can put it in again. However, leaving the form and re-entering causes the same thing to happen.

It's looks to do with the form as the table entry remains the same even when the form loads and shows no data.

Any ideas please?

Thank you in anticipation..

 

[jammin140900]

Re: Going insane- Subform going back to first record upon refresh

Hi All,

Going a little insane now. Using Lagbolts solution below, sometimes the solution works, sometimes it doesnt.

After the implicit requery occurs through Lagbolts code below, the second combo box requery's correctly and the data returns to the current line item. The problem is that sometimes when you select the next record below and enter a contacts name for that particular relating company, the whole datasheet requeries and all the contact people's names dissapear from all the records above apart from the current record.

A more frequently occuring variation when it feels like it is when you move your mouse over the different contact people's names (without clicking anywhere), the names just disappear off the screen like magic apart from the last record. The data is still being recorded in the table, but cannot be seen on the screen. Sometimes, closing the form and reopening it works, other times it doesn't..

Please help!!!! Going crazy with the variations to this problem!

Thanks
Mark