Access Query results in a form

I know, I know, why am I using Access?

Regardless, I am and there’s not a lot I can do about it for a few months.

I’m using a form that has a number of subforms which display the results of queries in datasheet view and allow the user to export the results rather than have the database open a succession of queries and leave the user to get on with it.

I’m basically cross referencing user details from various systems, so while things like firstname, surname and login are standard, other details such as access levels, financial authority etc vary from system to system.

What I’m trying to figure out is how to get the form displaying the results to display all the correct fields in the query rather than having to limit my standard results to the fields that are going to be constant every time the query is run.

Each subform is bound to the relevant query, but I appear to have to use the field list to create the relevant fields to display. However the contents of the query, and hence the field list, can change each time it is run but the fields on the form remain set to the original layout.

Does that make any sense?

I haven’t dealt with Access much (just ODBC linked tables pretty much), but in SQL it you’d probably use CASE WHEN <condition> THEN <action> END AS <fieldname>

SELECT table1.id,
CASE WHEN LEN(id)>1 THEN table1.firstname ELSE othertable.firstname END AS firstname
FROM table1
INNER JOIN othertable ON table1.id=othertable.id
WHERE id IN (your query)

I’m not sure that’s quite it, its the form rather than the query itself that’s got me flummoxed. Basically I’m cross referencing user account lists from various systems against some standard criteria to produce 4 standard outputs:

  1. A matching list against XP accounts and internal staff directory
  2. a list of accounts that can’t be matched to the above.
  3. A list of the accounts from 2) that match a database of people who’ve left the company
  4. A list of all the accounts that don’t match either 1 or 3.

The original SELECT tablename.* what is causing the problem at the moment because it’s taking (and I want) everything from the initial account list. However that “everything” can vary a lot from system to system, I might get:

login, firstname, surname, access level, financial authority, credit checking from one system and
login, firstname, surname, accountstatus, last used from another.

The first three fields will always be constant, but I want in my final form the other fields from that initial list that are not always the same, and it is this that is causing the problem because I haven’t figured out how to make the form update to reflect the change in content in the query.

So you’re chaning the query and need it to reflect on the form? Have you tried something like Me.Requery in VBA whenever you’re chaning the query?

I hadn’t, my google-fu might just be weak but it took me long enough to figure out how to display query results in a form to begin with (I came to Access VBA via Excel so I’m learning Access forms while I tear my hair out, the VBA and SQL is fine).

At the moment I have a Main form, with each query set as the record source to a separate sub-form. The only way I could figure out how to get the query to display on the [sub]form was to use the Field List and drag and drop the fields onto the form in question and set it’s default view to Datasheet, hence the non updating problem, when the query fields change, it does not reflect on the subform which merely retains the original format that was saved.

The vba itself merely writes a number of pre-defined queries based on criteria selected by the user on the one form I’ve managed to coax into working so far. I then wanted, rather than have to fire up 4 separate query windows and trust the user to know which data was what, to have a separate form open up that lets them view all the query results on a single page and export the relevant results into a spreadsheet or CSV file.

Getting the queries to display properly has been the hardest part so far.