CODEWITHSUNDEEP
formsvbams-access-2003
Jim
Jim

Reputation: 117

Access bug where a query sees blank for a form field

Using access 2010 on an access 2003 database.

I have a form with an unbound data field. A button calls vba, which calls a macro. The macro calls a query, which references the form data field [Forms]![my form]![my field]. Not actually called those things but just to explain it.

The vba code can see the form data field. Debug print prints it out. The query returns a blank.

I verified this with creating a new query that just lists out the table rows from a random table and also the [Forms]![my form]![my field]. I even verified it by changing the name, which makes the query prompt me for the data, and referencing another field.

This was happening yesterday on another form/query, but after messing with it, suddenly it started working. I'd compressed/fixed the database, added a replacement unbound data field, changed the data format from short date with datepicker to general date to just blank for the format. I'm still not sure why it suddenly started working.

But now it's happening on this other form and query, and I have no idea why.

I wasn't even editing the form when it started happening. Yesterday this form worked fine.

Any ideas why this could be happening? I might be able to keep fixing it, possibly, but it's not a good thing to have queries mysteriously stop working because the form field suddenly starts returning blanks.

Upvotes: 0

Views: 2142

Answers (1)

Wayne G. Dunn
Wayne G. Dunn

Reputation: 4312

I hate to put this as an answer because it is still a work-in-progress, but I can't fit my response into a comment box.

I did as you suggested (kinda):

  1. Created a new form with NO recordsource.
  2. Added two text boxes. The first started as text, but contained a date value; later formatted as date. The second just text.
  3. Created a query to access some table and BOTH textboxes from the form.
  4. Ran the query. And things all seemed to work. Played around changing value of date, format, etc. and it continued to work... until it decided not to work.
  5. No idea why it stopped working, but I kept tinkering.
  6. At one point, I started clicking into the date field in the query and was surprised to see some weird characters appear >> ꀀ䃕

  7. Then decided to modify the query to return the date as follows:

    Expr3: Format([Forms]![Form4]![Text0],"mm/dd/yyyy")

  8. And everything is now fine (until it breaks again? Maybe?

Upvotes: 1

Related Questions