Pre-loading a combobox or setting its default value via VBA

[roaftech]

Q: How to pre-load a combo-box or set its default value with a value passed from another form.

Context:
I have a database of aid donations which includes two main tables: 'DataPersons' and 'DataHholds'. These tables have a relationship established as
[DataPersons].[HHoldref] = [DataHhold].[HholdID]


where HholdID is the primary key for the DataHhold table. This is a one-to-many relationship as a household can comprise more than one registered person.

A form, selected from the switchboard, gives options for a report based on DataHhold - these options include viewing the report, printing, writing to pdf, etc. The particular household is selected by a combobox on that form. All works as intended.

I have another form, also selected from a different switchboard menu, which allows editing of the records in DataPersons - again all fine so far.

I would like to be able to move directly between these two forms, without having to go back through the switchboard - specifically, with the Persons form open and a particular person selected, to then open the reports form with the appropriate data for that person's household. To do this I have a button on the Person form which calls the Household form and modifies the query on which it is based. This works fine, except that the combobox on the household form remains blank and the buttons produce an empty report. I have to select the household again (although there is only one listed) before the reports can be seen.
What I want to do is to pre-load the combobox with the data from the first record returned by the query; ie something like
[forms].[HholdSelect].[combobox] = [forms].[fP1PersonReview].[HholdID].

I can hard-code the default value of the combobox manually by using
Me.[combobox].default = 234 where '234' is a typical HholdID but the following attempt at automation does not work
Me.[combobox].default = [forms].[fP1PersonReview].[HholdID]

As a work-around I have added a simple textbox to the Hhold form, and attempted to give it a value, as per the attached code.

Private Sub Form_Open(Cancel As Integer)

    Dim stFormName As String: stFormName = "fP1PersonReview"
    Dim inHholdID As Double
    
    'Check if person review form is open, alter the data query if present.
    If SysCmd(acSysCmdGetObjectState, acForm, stFormName) = acObjStateOpen Then
       Me.ItemSelector.RowSource = "qHholdSelectRev"  
       inHholdID = Forms!stFormName!HholdID         << Debug error - cannot find form referred to
       'Me.SelectedHhold = inHholdID
    Else
       Me.ItemSelector.RowSource = "qHholdSelectAll"
    End If
    Me.ItemSelector.Requery
    Me.ItemSelector.SetFocus
End Sub

In each case, the debug procedure reports that it cannot find the PersonReview form, even though it has successfully found it just two lines earlier in the same subroutine and the form is still open on the screen.

Is FindFirst worth exploring? Any other advice?

[davegri]

       inHholdID = Forms!stFormName!HholdID

I don't think the string is appropriate above. The syntax requires an object name of the form, so:

       inHholdID = Forms!fp1PersonReview!HholdID

might work.

[ssanfu]

While the code above can be fixed, I would use the "OpenArgs" parameter of the "OpenForm" command......

[roaftech]

Thank you both.
The recommendation in post #2 has resolved most of the problems, here and elsewhere. It seems curious that sometimes Access recognises dynamic form and report names but in other places it doesn't. There must be some logic (it's a computer routine!) but I haven't figured it out.
Also, what is the difference between [forms]![formname]![control] and [forms].[formname].[control]? That, too, seems inconsistent.

[Minty]

The difference between the Bang ! and the . Dot notation is a bit of a marmite subject, with some interesting views on it.
There is a thread here https://access-programmers.co.uk/for...ad.php?t=28739 on it and here http://rogersaccessblog.blogspot.co....-in-forms.html

[roaftech]

#3 Ssanfu - I couldn't open your speciment database itself but your comment about OpenForm encouraged me to re-examine my syntax. I found that using my LinkCriteria as a Filter condition rather than Where clause has resolved the outstanding problem.
#5 Minty - I happen to rather like Marmite in small doses. Thanks for the links - looks like the subject might be PhD study in its own right!

[ssanfu]

Sorry about that.
I didn't even look to see what version of Access you use.

Out of curiosity, can you open this mdb???

[roaftech]

Steve,
Yes, I can open the database, but I have a problem with it

Sub Form_Open(Cancel As Integer)
    Dim rst As DAO.Recordset   <<< Cannot find project or library
    Dim lngHholdID As Variant

However, I suspect that this is because of my configuration of Access. I am using Office 2003 - it's the latest version for which I have a legimate copy and that does not contain the necessary library. Nevertheless, I can read your code and that is very helpful.
Without wishing to sound pious, I find that XP and Office 2003 are still adequate for my needs and I am reluctant to spend money that might (quite literally) be used to buy food or firewood for somebody.
Many thanks for your time and your willingness to help people on this forum.
Happy New Year!

[ssanfu]

In Access 2000, 2002 & 2003, you need to set a reference to "Microsoft DAO 3.6 Object Library".
(in the IDE, TOOLS/References)

Without wishing to sound pious, I find that XP and Office 2003 are still adequate for my needs and I am reluctant to spend money that might (quite literally) be used to buy food or firewood for somebody.

I agree with you - I would still be using XP and Access 2000 (I am at home), but I was forced to convert to Win 7 and Access 2010 at the office.
(notice I said "Convert, NOT upgrade )