Form based on query or table? (1 Viewer)

AC123

New member
Local time
Today, 12:41
Joined
Apr 22, 2020
Messages
8
Hi!

I am new to access and I am really struggling to understand some concepts.

I am trying to create a to do list (attached) and I am struggling to understand whether the form should be based off a table or a query?
I have tried using a query however I cannot add data, only view it. In this case, what is the use of queries?

Thanks for any help you can provide
 

Attachments

  • Do List DB v2.accdb
    1.1 MB · Views: 93

theDBguy

I’m here to help
Staff member
Local time
Today, 05:41
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF!

So, what happens if you try to use a table instead? Normally, it doesn't matter if you use a table or a query, as long as the data source is from a single table only.
 

Micron

AWF VIP
Local time
Today, 08:41
Joined
Oct 20, 2018
Messages
3,476
There are many things that can cause a query to be read only.

I almost always base a form on a query because the query is very adaptable. If a query doesn't work for me, I don't build a form for it straight off. Forms before queries doesn't make sense to me, but not everyone will agree with that.
 

Isaac

Lifelong Learner
Local time
Today, 05:41
Joined
Mar 14, 2017
Messages
8,738
I was able to use your DB to enter a new record by pressing the icon shown in the bottom of the picture
Testing 20200709.jpg
 

AC123

New member
Local time
Today, 12:41
Joined
Apr 22, 2020
Messages
8
Hi all thanks for the replies.

The current DB is based off a table however I have read that they should be based off of queries. Have I got the wrong end of the stick here and it is ok to base it off of a table?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:41
Joined
Oct 29, 2018
Messages
21,358
Hi all thanks for the replies.

The current DB is based off a table however I have read that they should be based off of queries. Have I got the wrong end of the stick here and it is ok to base it off of a table?
Hi. When you create a bound Form, the Record Source can be based on a Table or a Query. Which one to use mostly depends on the requirements, but also sometimes based on your preference. I don't think there's a hard rule about it.
 

AC123

New member
Local time
Today, 12:41
Joined
Apr 22, 2020
Messages
8
Hi. When you create a bound Form, the Record Source can be based on a Table or a Query. Which one to use mostly depends on the requirements, but also sometimes based on your preference. I don't think there's a hard rule about it.
Thanks theDBguy. This issue was linked to the fact that I am trying to hide rows that have "yes" in the completed column. I have tried doing this with the following VBA code:

Private Sub Command119_Click()

If Me.Completed = "Yes" Then
Me.Completed.Visible = False
Else
Me.Completed.Visible = True
End If

End Sub

However on google searches it has been suggested to use a query?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:41
Joined
Oct 29, 2018
Messages
21,358
However on google searches it has been suggested to use a query?
Yes, in that context, I would agree with Google. As I said earlier, it depends on your requirements. So, if you require to filter out certain records from the form, then you can use a query. Of course, you also have the option to continue to use the table and simple apply the filter to the form. So, in that sense, it goes back to personal preference.
 

plog

Banishment Pending
Local time
Today, 07:41
Joined
May 11, 2011
Messages
11,613
I am trying to create a to do list (attached) and I am struggling to understand whether the form should be based off a table or a query?

My advice is to base forms that interact with data (add/edit/delete) off of tables--it just avoids a lot of issues like the one you encountered.
 

Isaac

Lifelong Learner
Local time
Today, 05:41
Joined
Mar 14, 2017
Messages
8,738
In a way, the differences mentioned here will end up being largely semantics for you, IF you end up needing to filter the form at any point, which is very likely. At that point you will probably want to dynamically set a SQL recordsource for the form anyway - like
Code:
Me.Recordsource="select * from table where fieldname=something"
And once you do that, you'll be essentially causing a Query to underly the form, rather than a table.

What I do sometimes is start off selecting a Table as the form's recordsource in Design View (or some very broad based query), and then using additional mechanisms to filter it later on.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:41
Joined
Feb 28, 2001
Messages
27,001
For a personal to-do list that isn't based on a split front-end/back-end, tables vs. queries is often tomayto, tomahto. For a split DB that will share access to the BE portion, queries often have the edge in preference and reliability. Depending on circumstances, particularly if the BE is in a domain environment, queries might actually be NECESSARY. I've had issues related to forms where they would not work when directly linked to BE tables, but if I built a single-table query, it worked. We have seen others on this forum report the same behavior but it is elusive as to WHY it does what it does.
 

mike60smart

Registered User.
Local time
Today, 12:41
Joined
Aug 6, 2017
Messages
1,899
Hi

Because a Customer can have Many Tasks I would recommend that you use a Main Form based on tblCustomers and a Subform based on tblTasks
 

Users who are viewing this thread

Top Bottom