Problem amending a forms RecordSource via code

[cascot]

In my forms Load event I make a call to a sub procedure which sets the forms RecordSource using the following code

Me.RecordSource = "SELECT * FROM MyQueryName " _
                & "WHERE FieldId=" & CurrentId

The RecordSource of the form is set as expected. If I call the code again with the same value for CurrentId things remain fine, however whenever I change the value of CurrentId and call the code the resultant recordset always contains zero records. If I run the exact same piece of SQL as a seperate process with the same value for CurrentId it returns the record I expect. It's as if all subsequent calls to the code to amend the RecordSource within the form are resulting in the forms existing Recordset being queried with the new criteria rather than the recordset being completely reset.

Can anyone offer any suggestions on wher I am going wrong, or which settings/properties may be causing this?

 

[stephenk1973]

Are you re-calling the code of the Form_Open? If so could try changing the form record source after after updating the id

Private Sub CurretnID_AfterUpdate()

Me.RecordSource = "SELECT * FROM MyQueryName " _
& "WHERE FieldId=" & CurrentId

End Sub

Might be worth creating a subfunction that can be call on opening the form and if the Id is changed.

Hope this helps.

Stephen

 

[cbsm]

Have you tried running in debug mode ?
This way you can follow the value of CurrentID, and then see if the source is not updated again in an unexpected place ?
(It might work differently in debug mode, as in "run-time" but it's probably worth trying).

 

[cascot]

stephenk1973 & cbsm,

Thanks for your replies.

I have a module scope variable that holds the value of CurrentId. A value gets assigned to CurrentId within Form_Open. Form_Load then calls a procedure called SetFormRecordSource which contains the code quoted in the initial post. As I said before that works as it should, however when another user driven event occurs which leads to the value of CurrentId being changed and Form_Load to be called again (which in turn calls the SetFormRecordSource procedure), so I now want the forms RecordSource to reflect the new value of CurrentId, I alsways get zero records returned.

Any help would be much appreciated.

 

[stephenk1973]

I've never stored values as 'module scope variables' so i'm not sure quite how your working this, but it sounds like the storred CurrentID is not being updated, hence when the SetFormRecordSource procedure is run it's not getting an input value. To confirm this i'd put 'Msgbox CurrentId' at the start of the SetFormRecordSource procedure to echo and make sure it has the value you expect.

I would usually work on setting the CurretID form field and then setting your forms record sourse based on it's value.

Stephen

 

[cbsm]

Where exactly is currentID stored ?
When you say "Module" - do you mean in the code behind your form, or in another module altogether ?
Can you post the exact code of all this ?
(where is currentID declared, where is the SetFormRecordSource procedure etc...).
And for sure, going through this in debug mode, or - as stephenk1973 said, adding Msgbox here and there, with the value of currentID will help find you out where the problem lies.

 

[cascot]

Thanks again for your replies.

I declare the CurrentOrgId variable in the form's general declarations, but CurrentOrgId containing the correct or incorrect value is not the cause of the problem.

There's quite a bit of other code in my form unrelated to the problem, so I think the best thing to say is that where before I had the following code

  Me.RecordSource = "SELECT * FROM qCRMMain_OrganisationView " _
                  & "WHERE OrganisationId=" & CurrentOrgId

and the problem I detailed earlier was evident, I now have the following code (all other code in the form remained the same), and it works as expected

  Dim rs As Recordset
  Dim SQLcmd As String
  SQLcmd = "SELECT * FROM qCRMMain_OrganisationView " _
                  & "WHERE OrganisationId=" & CurrentOrgId
  Set rs = CurrentDb.OpenRecordset(SQLcmd, dbOpenSnapshot)
  Set Me.Recordset = rs

Does anybody have any thgoughts on why simply amending the RecordSource, as I was initially doing, was not working while amending the Recordset does?