Continuous Form Not Updating Tables Correctly (1 Viewer)

[RainLover]

plog

I can't allow a challenge like that go unanswered. You have stated that one should never use a query as a record source for a table. I have never heard of this before. I was wondering if you knew something I did not.

It we build the First table of our database it may look like this. I shall keep this overly simple.

tblEmployees

EmployeePK AutoNumber

FirstName Text
MiddleName Text
LastName Text
AddressLine1 Text
AddressLine2 Text
DOB Date

Up to now all of these fields belong in tblEmployees. I don't think anyone would disagree so far. So now lets add some repeating Data.

CityFK Number Long

So we have one table with One Foreign Key. We need a Second Table

tblCities

CityPK AutoNumber
City Text
PostalCode Text
State Text. This should actually be pushed out into a third Table in order to comply with normalisation.

Next thing I want a Form with all the above information plus a sub Form that holds some other information that is related to the Employee. We could have Jobs completed. I would need

A Primary Key Autonumber, JobNumber which would be a foreign key to yet another Table. DateCompleted, HoursTaken and say Comments.

We could keep going but this should be enough to demonstrate my design preferences.

In order to create the Main form I would need Data from tblEmployees, tblCities and perhaps a third table if you wanted to include a state.

You Said No Queries. I say you can't design a form that is properly Normalised as I just did without joining two tables with the aid of a Query.

I wrote this rather quickly. I hope I said things right.

 

[RainLover]

plog

It is early morning for me so I am calling it a night.

We are in no rush so I will read your response tomorrow.

 

[plog]

I followed what you were laying out with your example, but I don't see how a query is necessary. I was with you up until this line:

In order to create the Main form I would need Data from tblEmployees, tblCities and perhaps a third table if you wanted to include a state.

I do not believe that statement is correct. With the table structure you have given, I would create this form system:

Main Form: Source=tblEmployees
1. Inputs for all fields you initially listed
2. Combo box for the CityFK (based off tblCities)

Sub Form: Source=tblJobs
1. Linked to Main Form by appropriate Parent/Child fields
2. Inputs for all fields you listed this table has

I follow the example you have laid out in the sense that I understand your scenario. But I don't reach the conclusion you are going for. I just don't see how it demonstrates that a query is necessary to build forms upon.

 

[RainLover]

plog

An understanding of your method came to me while sound asleep.

I do not like your method or the reasoning behind it. However I also cannot find sufficient reasoning to argue against it.

I have always used queries and believe them to be more flexible. I also have the option of converting my query into a SQL statement and then stick it behind the form or other object that may have my attention at the time.

In doing this I can then convert the entire Database into an mde file. once this is done the user has no access to what has been written. They can see the Tables and the queries. nothing else. because there are now no queries the user is further limited.

I could list a few more advantages from my point of view but this will not change your way of thinking.

With that I wish you well and thank you for showing me something a little different.