Field reference lost until Design Mode/VBA? (1 Viewer)

[David R]

This is a really weird one (I think I say that every time I make a thread anymore, just goes to show how much I've learned here...)

I have a form/subform that requires some fields from the Switchboard for its criteria. Last week due to no action on my part, it started asking for those values as Parameters instead (Forms!Data!txtInspDate), etc! The new form loads, but of course it is blank with no data. This happens if I load it from a command button or from the navigation sidebar... UNLESS I first load the switchboard in Design Mode, go to VBA window, then reload the Switchboard in Form Mode and press the button. From then on until I close the database, everything works fine!

I've tried compacting and repairing. I think I've looked at all of my references in the new form and the query it is based on... I've had this happen once or twice before over the years but can't remember the solution, and search is failing me due to the general nature of the problem.

Any ideas?

 

[David R]

Bump? Any ideas? Only learned two things so far:

1. opening the VBA window in general will stop the error; it does not have to be for that form.
2. I've got the field listed in some places in VBA as Forms!Data!txtInspDate and in some places as Forms.Data.Form.txtInspDate - but according to pbaldy, parameters have to use the bang notation, so that shouldn't be it. And as I said, this started with no code/query edits on my part.

 

[pr2-eugin]

In the first post, you mentioned...

I have a form/subform that requires some fields from the Switchboard for its criteria.

So does the SubForm require these parameter values?

Maybe there is some VBA CODE that changes the recordsource of the Subform behind the button click, but the main form had its own filter on the Subform in design mode??

 

[missinglinq]

From your original post, it sounds as if the Switchboard is closed when you open the secondary Form; is this the case?

Linq ;0)>

 

[David R]

In the first post, you mentioned...So does the SubForm require these parameter values?

Maybe there is some VBA CODE that changes the recordsource of the Subform behind the button click, but the main form had its own filter on the Subform in design mode??

I'm 99% sure at this point it's not the main form, but the subform. I changed one of the field references in its main query and it changed the parameter prompts.

There's no recordsource changes going on... and unfortunately if I bundle up the database with local tables, the problem goes away entirely! In fact just commenting out this seemingly unrelated segment of code makes the form load just fine:

Private Sub buttonAssignments_Click()
Dim locfile, loc As String
Dim tim As String

'    tim = Format(Me.txtInspDate, "yyyymmdd")
'    locfile = DLookup("[Database]", "MSysObjects", "[Name] = 'ImportTemp' And [Database] Is Not Null")
'    loc = Mid(locfile, InStrRev(locfile, "\") + 1, 255)
'
'    If InStr(loc, tim) = 0 Then 'no match, something might be wrong
'        If MsgBox("Inspection date of " & Me.txtInspDate & " does not match nsassign list date (" & loc & "). Stop and rerun/reimport?", vbYesNo + vbQuestion, "Possible mismatch on inspection data") = vbYes Then Exit Sub
'    End If
    
    'allow it to go through if it can't find it, or no match, or whatever
    DoCmd.OpenForm "formReview"
End Sub

 

[David R]

From your original post, it sounds as if the Switchboard is closed when you open the secondary Form; is this the case?

Linq ;0)>

Negative - what I meant to say was that formReview spawns the error regardless of whether it's triggered from the Switchboard or the nav panel. Sorry for any confusion there.

 

[missinglinq]

I was trying to ascertain if the Form being referenced was always open when the you're referencing it. You cannot reference a Control on a Form unless it is Open and Visible.

Linq ;0)>

 

[David R]

I was trying to ascertain if the Form being referenced was always open when the you're referencing it. You cannot reference a Control on a Form unless it is Open and Visible.

Linq ;0)>

Yes, absolutely (and open in Form Mode, before you ask). It's the Switchboard for the database, even though it has a different name (I inherited this piece of junk and rewrote 90% of it, but all of this junk, I mean code, is mine).

 

[David R]

Attaching a version that WORKS, in the hopes some new eyes might spot what might be triggering the error anyway. formReview is the one that gives problems occasionally...

 

[missinglinq]

Sorry; you must be running 2010 or 2013; I can't open it in 2007.

Linq ;0)>