Forms, better to be based on queries than tables?

[Chaz]

Just wondering / pondering while developing this database ive chosen to tackle .... is it better to base forms on queries that are easier filters than 'dumb' general info in tables?

 

[SOS]

Normally I would say that you should use a query for a form/report as opposed to just setting the table name in there. For forms, the important piece can be that you can sort the query whereas the table is just a potential mixed up set of data (Access doesn't store records in any particular order in the tables and you can get a varied view each time you open it, depending on what has been added/deleted, etc.).

Also, you can use queries to limit the amount of data coming in which can enhance performance. Only pull what you need.

 

[Chaz]

Thanks, so you are tending to agree with my thought I take it then?

 

[SOS]

That would be a YES.

 

[Banana]

There is another good reason.

If you just drop in a table name and use filters, you are likely hitting the backend twice; one for the full table which is going to get discarded as you return for the filter.

Only use filter when you actually want to give users ability to filter out the data (and even so, it's hard to find a legitimate scenario; we easily can just use a form to input the filter and execute an query & open form based on that query instead, doing only one hit rather than 2 hits).

 

[SOS]

...only one hit rather than 2 hits).

And I know that when I get hit I'd rather only get hit once than twice <lol> (sorry, I couldn't resist)

 

[Kryst51]

Cheesy....But I'm smiling.

 

[Chaz]

Ok, I have an issue but not sure if its related to this Table - Query - Form.

I have users inputting data and when they go back into another form that uses the same table source, detail is missing.

I am using queries to filter data - could the query cause data not to be entered into the table?

Im also having multiple user issues (Access is locked by Admin message) despite having split the database, which Im trying to fix, not sure which is causing the issue.

Any help appreciated.

Thanks.

 

[SOS]

when they go back into another form that uses the same table source, detail is missing.

If that form is open at the same time the other form is you would need to requery the form they are going back to. When you open a form it opens with a recordset of available records. Unless you tell it to requery, or close and reopen, it will not have the new records.

 

[Chaz]

If that form is open at the same time the other form is you would need to requery the form they are going back to. When you open a form it opens with a recordset of available records. Unless you tell it to requery, or close and reopen, it will not have the new records.

Thanks, think I know what might be happening. In Access Advanced options, should I be using default record locking - option edited record?

I tried that earlier but seemed when people were using the 'tracker' form and filtering, they were locking out other users - at least thats what seems to be happening.

 

[SOS]

Thanks, think I know what might be happening. In Access Advanced options, should I be using default record locking - option edited record?

I doubt it. You need to be able to requery the other form if the user closes the one form. But if they aren't closing and just moving from form to form, you need some way of requerying but I'm not sure what you would do to make it happen.