vba magically picks up data from form text box that isn't there

[DreenG]

first ever post - not sure if this should be in programming or forms
Hope I've supplied enough info and it is clear enough

I have created a small database part of which is a jobs to do list. There is a precis form of this list (dgF_ToDo) and a more detailed form (dgF_Events). A user can double-click on one of the jobs in the precis list to display the more detailed list.
The only events associated with both forms are the double-click event on the precis list and the open event on the detailed list. There are no 'public' variables.

*The VBA code for the double-click event on dgEventData on form dgF_ToDo is : (I've numbered the lines for reference)

1 If Not IsNull(Me![dgCategory_tb]) Then dgcat = Me![dgCategory_tb] Else dgcat = "no cat"
2 If Not IsNull(Me![dgSubCategory_tb]) Then dgsubcat = Me![dgSubCategory_tb] Else dgsubcat = "no subcat"
3 If Not IsNull(Me![dgItemName_tb]) Then dgitem = Me![dgItemName_tb] Else dgitem = "no item"
4 If Not IsNull(Me![dgEventRef_tb]) Then dgEventRef = Me![dgEventRef_tb] Else dgEventRef = "no event ref"
5 dgWhere = "[dgEventRef] = " & dgEventRef
6 dgwstring = dgcat + "*" + dgsubcat + "*" + dgitem + "*"
7 dgopenargs = dgwstring
8 DoCmd.OpenForm "dgF_Events", , , dgWhere, , , dgopenargs

dgF_Events OPEN event deconstructs the argument string (dgCategory, dgSubCategory, dgItemName), opens the form and populates unbound fields in the form header (dgCategory_tb, dgSubCategory_tb, dgItemName_tb) and dgEventData_tb in the form 'detail' section.

It all works OK BUT…



Where does it get this data from in line 1 ??? --- it's in the underlying query but there is no [dgCategory_tb] on the form. (ditto line 2 as well).
Me![dgCategory_tb] just doesn't exist !
Yet it works just fine.
Stepping through the code execution picks up the category successfully. How ??
dgF_Events then populates Category & SubCategory from the supplied arguments without a problem.

If I comment out line 1 then dgF_Events displays OK but with "no Catgeory specified" in that field.
If I try to set the focus on dgCategory on form dgF_ToDo (in an desparate attempt to find it on the form) it says 'can't do it' ! - Of course it can't - it's not there. Until I set dgcat to it - then suddenly it's there !?!?
TILT !!

I must be missing something fundamental here and am now so locked into this I can't seem to get out of this thought track. I'm stuck.
Can anyone help ? I hope I have provided enough information.

Here are the relevent parts of my Db : (or at least the relevent bits of them)
dgT_ToDo

• a table with fields (amongst others) :
  
  • dgdate
  • dgRefNum (autonum)
  • dgCategory
  • dgSubCategory
  • dgItemName
  • dgEventData
  • dgCreationDate

dgQ_ToDo

• query just orders all the data in table 'dgT_ToDo' appropriately.

dgF_ToDo

• continuous form based on a single query dgQ_ToDo with job (event) precis information - fields are :
  
  • dgdate_tb - populated by Date()
  • dgRefNum_tb (autonum)
  • dgItemName_tb
  • dgCreationDate_tb
  • dgEventData_tb

dgF_Events

• single form with more detailed event informaton includes the following fields (amongst others) :
  
  • dgCategory_tb
  • dgSubCategory_tb
  • dgItemName_tb
  • dgEventData_tb.

[June7]

Perhaps you should provide db for analysis. Follow instructions at bottom of my post.

[DreenG]

it's all confidential data.
I've really supplied all the data I can.
It all boils down to how can vba pick up data from a text box ([dgCategory_tb]) when that text box is not on the form.

[June7]

Trying to comprehend by just reading description isn't working for me. I cannot follow this without reviewing db structure and form design. Don't really need data, at least not real data. As advised in instructions, make copy and remove/change confidential data.

[DreenG]

Many thanks VIP for your replies to date -

I have reworded the issue in an attempt to 'generalise' it or simplify it.
I hope this is more understandable. Perhaps I supplied too much info and ended up hiding the simple problem.

here's my re-word ---
I have a form (form A)
It shows a continuous set of records.
There is only 1 'event' associated with this form
This is a double-click event on one of the fields (text boxes] on the form
This event opens another form (Form B).
The Form A double-click event procedure code picks up data from Form A :
If Not IsNull(Me![dgCategory_tb]) Then dgcat = Me![dgCategory_tb] Else dgcat = "no cat"
and supplies this (dgcat) to a DoCmd.OpenForm as an argument :
DoCmd.OpenForm "dgF_Events", , , dgWhere, , , dgcat
The 'open event' procedure code for Form B successfully uses the data from the DoCmd.OpenForm argument.

It works OK every time.

BUT
There is no such text box as 'dgCategory_tb' on Form A !!

Yet stepping through the code in debug mode dgcat gets immediately assigned successfully with correct category (i.e. not "no cat")

How is this possible ??

[Welshgasman]

I would have to say 'impossible'?
Do you have Option Explicit at the top of this form module? and all others?

[Micron]

I agree with both responses. However, try this: in form design, click on combo in property sheet and look for the control in the list that you say doesn't exist. You might find it there and if chosen it will be come the active control. Failing that, you could open a new db and import everything into it. Your system table that keeps track of objects might be corrupted, but I would expect the form to raise an error when opened.

If you post a db, consider removing all that is not necessary and for the 5 or so records that would allow the form to open, consider using this:
https://www.accessforums.net/showthread.php?t=77482

[DreenG]

thank you for your reply Welshgasman.
I would say 'impossible' as well.
Alas I'm not a 'novice' as it says under my name.
It's worse than that.
I've probably written well in excess of 50,000 lines of vba for lots of databases - all work ok - but a vba expert would probably faint with horror when looking at my Db structures & code.
I have no idea what a 'module' is other than the code in it seems to be available to procedures associated with other forms in a database.
And, to compound the felony, I know squat about oop. I'm a self 'learnt' vba programmer - and I use the word 'learnt' in its broadest possible sense !
I've also just realised I addressed June7 as VIP !

In answer to your Q - yes - I have Option Explicit at the top of 3 of my modules and 'Compare Database' at the top of the fourth. But the code I am referncing is local to the 2 forms involved. I should come clean and admit this is only part of a rather bigger database but I was trying to keep it simple since nothing of the rest was involved.
The bit I don't get is stepping through the code in debug the part line :
#If Not IsNull(Me![dgCategory_tb]) #
is highlighted in yellow and if I hover the mouse over it then the correct 'category' value is displayed ! which then proceeds to get assigned to dgcat ok. Impossible right ?

[Micron]

One thing I would virtually never do is write Me!anything as opposed to Me.anything. Any error with respect to whatever follows ! will only be caught at run time, and only if that line is executed. If it's incorrect, it will always escape compiling so you'll never catch the error when compiling.

[DreenG]

Dear Micron - BRILLIANT
I'd looked everywhere on the form including trying to set the focus to it in an attempt to find it.
Your suggestion led me to click on the drop down box top left in the property sheet. I never thought of that. It worked. There it was dgCatgory_tb - I clicked on it and suddenly - underneath another text box I could see those orange drag squares - I moved the text box out of the way and there it was - as a single line !
I'd forgot I put it there and reduced it down to be invisible but available.
I'm too clever for my own boots I am !! So my mum always told me. She may have been on to something !
Many thanks again Welshgasman
Problem solved. And thanks again to those who replied - how do I mark this as solved?

[Welshgasman]

Is that also the name of the field?

[pbaldy]

Research bang vs dot. I am far from an expert, but in general terms in this instance bang (!) is referring to the underlying record source, which presumably includes that field. You'd get a compile error if you switched it to dot (.) since the control doesn't exist. In other words:

Me![dgCategory_tb] refers to the field in the recordsource
Me.[dgCategory_tb] would refer to a control on the form

[June7]

Thread Tools at top of first post. Done.

After reading Micron's response, seems so obvious now. Guess I was rushing through the original post and trying to take it all in.

[pbaldy]

Oops, looks like you already resolved it. My point remains valid, you don't need a control on the form to refer to a field in the recordsource (just tested to make sure).

[DreenG]

I meant to say "Many thanks again Micron" at the end of my last post...
and I don't know the difference between Me!anything and Me.anything.
to answer Welshgasman
I name all fields .... text boxes as <control name>"_tb", combo boxes as <control name>"_cb", tables as "dgT_"<table name>, queries as "dgQ_"<query name> etc etc.
thanks again everyone.