Requerying Recordset Based on Combobox

[bullfrog83]

I am trying to requery a recordset so that a form changes record based on a combobox. In the form's Open event I'm using this code:

    Dim cn As New ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim strSQL As String
 
    cn.Open CurrentProject.Connection
 
    Set rs = New ADODB.Recordset
 
    strSQL = "SELECT * FROM StdDegReq WHERE PersonId = " & Nz(Me.txtPersonId, 0)  
 
    rs.Open strSQL, cn, adOpenKeyset, adLockOptimistic
 
    Set Me.Recordset = rs
 
Exit_Procedure:
    cn.Close
    Set rs = Nothing
    Exit Sub
 

What I'm trying to figure out is how to requery the recordset in the AfterUpdate event of the combobox without having to repaste all my code from the OnOpen event (if possible). I tried this but it doesn't work:

    Dim rs As ADODB.Recordset
 
    Set rs = Me.Recordset
    rs.Requery
 

I get the error message: "Operation is not allowed when the object is closed". What object is closed? Any suggestions?

[nico5038]

Did you try:

Me.Recordset = Me.Recordset 
 

Nic;o)

[bullfrog83]

@nico5038
How do you mean? Just have that code in the module?

Also, I added the the error message that I get if I just execute the code above in my OP: "Operation is not allowed when the object is closed".

[nico5038]

This command will requery your form after activation.

The error messages does look like you're having some code to close the form. Check your code against the event sequence to solve this.

When stuck attach (part of) the .mdb so I can have a look.

Nic;o)

[bullfrog83]

@nico5038
If I only have Me.Recordset = Me.Recordset in the AfterUpdate event I get "Error 450: Wrong number of arguments or invalid property assignment." Perhaps I've misunderstood how you want me to include this in my code?

[nico5038]

Oops, typo, this needs to be:

Me.RecordSource = Me.RecordSource
 

Sorry.

Nic;o)

[bullfrog83]

@nico5038
I think that does the trick as I get no error but the problem is if Me.txtPeopleId is null it errors out. So, I put Nz(Me.txtPeopleId,0) so I don't get that error. However, from checking the code in the Immediate window it appears that the Nz function is returning 0 for any id I select. So, if I select id 1489 it's executing the code as PersonId = 0 instead of 1489. If not Nz, then what can I use to handle the variable if it's null?

[nico5038]

First I would "move" your code from the Open event to the OnCurrent event, thus making sure it's executed every time there's a change of record.

Next I would use this code to see or the combo is filled and issue a warning when no selection has been made:

IF Len(NZ(Me.txtPeopleId)) > 0 then
   Me.RecordSource = Me.RecordSource
else
   Msgbox "Please select a Person"
endif
 

Nic;o)

[bullfrog83]

@nico5038
If I move my code to the OnCurrent event I get an unexpected error has occurred and Access quits. This happens when the code gets to the line: Me.Recordset = rs (see OP).

[nico5038]

When it's a bound combo and filled when the form opens, then I guess a loop will occur.
So we'll need to use the OnOpen of the form and the AfterUpdate event of the combo.

Nic;o)

[bullfrog83]

@nico5038
Now it's working. Thanks!