Form Not Updating Tables 2

[mibeach7]

Hello,

I have a query that joins 3 tables. I created a form with fields from each of the three tables using the Select query.
When I click in the form field to add or change the data, nothing happens.

How do I postback to the table from a form? Is there a table, form, or field setting to enable updates?

Do I need VBA code for each form field. But shouldn't a Dynaset give this functionality?

 

[PHV]

Take a look at Updatable query

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mibeach7]

I changed the form recordset type to Dynaset (inconsistent updates). The problem is the form does not show the existing data from the tables, all the form fields are blank. Although I can add data to the form fields now.

Where is the current table data? Is there another setting?

 

[PHV]

How are you opening the form ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheAceMan1]

How are ya mibeach7 . . . . .

Post the query!

Sounds like it could be a problem with your joins . . . .

See Ya! . . . . . .

 

[willir]

mibeach7

Access wont let you update / insert / delete the data if it can not maintain integrity.

For example, you have a query that depicts both the one side and the many side in a one-to-many relationship. Depending on the query, Access may not allow you to add a record.

Reason: Well, the child record needs the primary key from the parent record as the foreign key, but the primary key for the new record is not avaiable. Variation of this theme may explain why you are having problems.

Richard

 

[mibeach7]

TheAceman1,
How do I post the query. In module?
Maybe on form load I need to set it the recordset to update?

 

[mibeach7]

Willir and TheAceman1,

Thanks for the insite.

I have three tables I am joining one to one on a date field. I do not outer join any of them.

 

[TheAceMan1]

mibeach7 . . . . .

You misunderstood me. Copy the SQL and paste it in your next post here on the [blue]Tek-Tips[/blue] forum.

If its in a module just copy it.

If its a query, go query design view and in the leftmost toolbar button, select [blue]SQL View[/blue] and copy from there.

We need to see the SQL!

See Ya! . . . . . .

 

[willir]

Try posting your query as suggested by the AceMan.

You need a table or a query to properly display records in a form.

I am assuming you have a query...

I have a query that joins 3 tables

If you used query builder to create your query...
- open the query
- click on the menu option "View" -> "SQL View"
- copy and past the code in this forum.

By the way...

I have three tables I am joining one to one on a date field

You need to have the primary key avaiable when you create a new record. No key, no record.

Richard

 

[mibeach7]

Here it is. What I have is 3 tables, one for each hairstylist. I am creating a scheduler for each time slot that will hold the name of the customer. I join on date so I can view all the time slots for a whole day. On the form, I have all time slots for each stylist as text fields.

SELECT STYLIST3.ID, STYLIST3.[9:00], STYLIST3.[9:15], STYLIST3.[9:30], STYLIST3.[9:45], STYLIST3.[10:00], STYLIST3.[10:15], STYLIST3.[10:30], STYLIST3.[10:45], STYLIST3.[11:00], STYLIST3.[11:15], STYLIST3.[11:30], STYLIST3.[11:45], STYLIST3.[12:00], STYLIST3.[12:15], STYLIST3.[12:30], STYLIST3.[12:45], STYLIST3.[1:00], STYLIST3.[1:15], STYLIST3.[1:30], STYLIST3.[1:45], STYLIST3.[2:00], STYLIST3.[2:15], STYLIST3.[2:30], STYLIST3.[2:45], STYLIST3.[3:00], STYLIST3.[3:15], STYLIST3.[3:30], STYLIST3.[3:45], STYLIST3.[4:00], STYLIST3.[4:15], STYLIST3.[4:30], STYLIST3.[4:45], STYLIST3.[5:00], STYLIST3.[5:15], STYLIST3.[5:30], STYLIST3.[5:45], STYLIST3.[6:00], STYLIST3.[6:15], STYLIST3.[6:30], STYLIST3.[6:45], STYLIST3.[7:00], STYLIST3.STYLIST, STYLIST3.DATE, STYLIST2.ID, STYLIST2.[9:00], STYLIST2.[9:15], STYLIST2.[9:30], STYLIST2.[9:45], STYLIST2.[10:00], STYLIST2.[10:15], STYLIST2.[10:30], STYLIST2.[10:45], STYLIST2.[11:00], STYLIST2.[11:15], STYLIST2.[11:30], STYLIST2.[11:45], STYLIST2.[12:00], STYLIST2.[12:15], STYLIST2.[12:30], STYLIST2.[12:45], STYLIST2.[1:00], STYLIST2.[1:15], STYLIST2.[1:30], STYLIST2.[1:45], STYLIST2.[2:00], STYLIST2.[2:15], STYLIST2.[2:30], STYLIST2.[2:45], STYLIST2.[3:00], STYLIST2.[3:15], STYLIST2.[3:30], STYLIST2.[3:45], STYLIST2.[4:00], STYLIST2.[4:15], STYLIST2.[4:30], STYLIST2.[4:45], STYLIST2.[5:00], STYLIST2.[5:15], STYLIST2.[5:30], STYLIST2.[5:45], STYLIST2.[6:00], STYLIST2.[6:15], STYLIST2.[6:30], STYLIST2.[6:45], STYLIST2.[7:00], STYLIST2.STYLIST, STYLIST2.DATE, STYLIST1.ID, STYLIST1.[9:00], STYLIST1.[9:15], STYLIST1.[9:30], STYLIST1.[9:45], STYLIST1.[10:00], STYLIST1.[10:15], STYLIST1.[10:30], STYLIST1.[10:45], STYLIST1.[11:00], STYLIST1.[11:15], STYLIST1.[11:30], STYLIST1.[11:45], STYLIST1.[12:00], STYLIST1.[12:15], STYLIST1.[12:30], STYLIST1.[12:45], STYLIST1.[1:00], STYLIST1.[1:15], STYLIST1.[1:30], STYLIST1.[1:45], STYLIST1.[2:00], STYLIST1.[2:15], STYLIST1.[2:30], STYLIST1.[2:45], STYLIST1.[3:00], STYLIST1.[3:15], STYLIST1.[3:30], STYLIST1.[3:45], STYLIST1.[4:00], STYLIST1.[4:15], STYLIST1.[4:30], STYLIST1.[4:45], STYLIST1.[5:00], STYLIST1.[5:15], STYLIST1.[5:30], STYLIST1.[5:45], STYLIST1.[6:00], STYLIST1.[6:15], STYLIST1.[6:30], STYLIST1.[6:45], STYLIST1.[7:00], STYLIST1.STYLIST, STYLIST1.DATE
FROM (STYLIST1 INNER JOIN STYLIST2 ON STYLIST1.DATE = STYLIST2.DATE) INNER JOIN STYLIST3 ON STYLIST2.DATE = STYLIST3.DATE;

Boy, this looks ugly eh!

 

[mibeach7]

I changed the primary keys to the joined fields. Created a new form addind the field from the tables without using the select query. And it works.
Thanks guys for your help!!

 

[willir]

mibeach7

Actually, although the SQL statement looks ugly, you have provided some beautiful information -- you provided us with context and your objective. This type of information is much more helpful -- it helps us avoid dead-end alleys and brick walls.

My first comment is that you are using a separate scheduling table for each stylist -- much, much easier to use only one stylist. Why? Well now you can query one table to find your info. For example - with three tables, finding out who is not working at 6:15 PM, you have to query each table. For example - your business takes off, and you hire two more stylists - do you add two more tables, edit all relevent queries, tables and forms?

A more typical approach would be to add the Stylist ID number or CODE to the schedule table.

...Now for a much tougher issue, which is probably how you got where you are. How to create the schedule(s).

This tough because there are several ways how to do this, and each one is complicated.

- You can use exceptions. Only track booked time slots.
- Have a schedule for each stylist and flag them as available, booked or not working
- Have a schedule for each "seat" in the salon, and assign the stylist to the "seat" for each working time slot. And assign a customer to the "seat".

The last idea seems to be the best. However, I know that stylists often only work at one "seat", and other stylist do not share. In a sense this seems almost like assigning booking to a stylist. But you can assign a free time slot to a seat without worrying about the stylist.

There are variations of this, and to be honest, at this time I do not know which is best. For example, indicate a sytlist is avaiable on a certain date from start time to end time.

Regardless, I would also move the "booking" times to a table -- this would allow you to "edit" time slices, and simplify your coding.

You can then create a template for each day of the week, and use the template to create schedules for each date. For example, use the TUE template to create a schedule of available bookings for July 13, 2004.

...Moving on
I setup a test database. The design was as follows...

StylistTbl
StylistID - primary key
StylistLN - last name
StylistFN - first name
+ info on the stylist - phone number, etc.

DayOfWeekTbl
DayOfWeek - primary key, text, 3 letters - SUN, MON, TUE...
DOWNumber - numeric 1, 2 ... for SUN to SAT, specific for coding

TimeSlotTbl
SlotID - primary key
StartTime - date (time, medium format)
EndTime - date (time, medium format)

TaskTbl
TaskCode - primary key, text, CUT_YTH, CUT_ADL, PERM_1
TaskCost

ScheduleTbl
SchedID - primary key
StylistID - foreign key to Stylist table
SchedDate - date (medium format)
DayOfWeek - foreign key to DayOfWeek, not sure if this is required
SlotID - foreign key to Time Slot table
Booked - text, name of customer
BookedPh - phone number or other info on customer
TaskCode - foreign key to Task table

...Now this is just at the top of my head, but I think it works.

I created a sample SQL statement using Query builder - looks like the following...

SELECT S.StylistLN, D.SchedDate, D.DayOfWeek, T.SlotStart

FROM DayofWeekTbl as DW 
INNER JOIN (TimeSlotTbl as T INNER JOIN (ScheduleTbl as D 
INNER JOIN StylistTbl ON D.StylistID = S.StylistID) 
ON T.SlotID = D.SlotID) ON DW.DayOfWeek = D.DayOfWeek;

With sample data (I used 30 min slots, not 15 min), it gave me...
[tt]
StylistLN SchedDate DayOfWeek SlotStart
Hope 07-Jun-04 MON 9:00 AM
Saunders 07-Jun-04 MON 9:00 AM
Morgan 07-Jun-04 MON 9:00 AM
Hope 07-Jun-04 MON 9:30 AM
Saunders 07-Jun-04 MON 9:30 AM
Morgan 07-Jun-04 MON 9:30 AM
Hope 07-Jun-04 MON 10:00 AM
Saunders 07-Jun-04 MON 10:00 AM
Morgan 07-Jun-04 MON 10:00 AM
Saunders 07-Jun-04 MON 10:30 AM
Morgan 07-Jun-04 MON 10:30 AM
Saunders 07-Jun-04 MON 11:00 AM
Morgan 07-Jun-04 MON 11:00 AM
Saunders 07-Jun-04 MON 11:30 AM
Morgan 07-Jun-04 MON 11:30 AM
Hope 07-Jun-04 MON 12:00 AM
Hope 07-Jun-04 MON 12:30 AM
[/tt]

Pretty rough, but I think this may be a better direction than the one you are heading.

Comments?

Richard

 

[willir]

...Sighhh

While I slaved away, you solved your problem ;-)
Great stuff!

 

[TheAceMan1]

mibeach7 . . . . .

If you intend to make any future improvements, or you find yourself considering redesign (I think you will), look into [blue]Table Normalization & Relationships[/blue] first, and get to know them well.

Table Normalization:

http://support.microsoft.com/default.aspx?scid=kb;en-us;283878

Table Relationships:

http://support.microsoft.com/default.aspx?scid=kb;en-us;304467

See Ya! . . . . . .