Create an "excel like" form

[jeffshex]

Hey all,

I'm trying to present my form to my users that look like an excel form (data sheet view). My problem is that it is a non-updateable query due to a Left Join on one of the 3 tables.

The table layout is like

--------      -------------      --------------
tblHours <--- tblClientInfo ==== tblProgramInfo
--------      -------------      --------------

I do this so I can display all the client names and a piece of information from the program info table and then just punch in what I need in the fields for the hours table. It seems quicker than having to navigate to each client, open the hours form and fill it out, then repeat for every other client.

I'm not sure if this is a form or query forum question, but I'm guessing I may have to do this with unbound fields and do an update/insert by VBA from the form, so that's why I posted it here.

Any suggestions?

 

[dhookom]

I expect someone could describe how to display the "piece of information from the program info table" and keep the results updateable if you provided more details.

Duane
Hook'D on Access
MS Access MVP

 

[jeffshex]

Ok, here it goes from the beginning:

Table fields

tblClientInfo     tblProgramInfo      tblHours
=============     ==============      ========
ClientID          ProgramID           HoursID
LName             ClientID            ClientID
FName             ParticipationDate   MonthDate
...               TotalHrsAssigned    Week1
                                      Week2
                                      Week3
================================================

I have a form where you select a date frame for the ParticipationDate from the ProgamInfo table and another bit that allows the user to select for what month/year they want to view hours for via the MonthDate.

After they select that, I'm having it open to a form where i would like it to display all of the clients who should be included from their particpation date.
On top of that, the form will allow the user to fill in other fields from the tblHours form. It should also display any info the user has entered in there previously.

Basically they selecting a group of people by their participation date and then selecting any hourly info for the specified month. That's where the left join is stopping me. I can get it to look right by showing all the info from the client table, but that's where it becomes non-updateable.

Does that help? Let me know if any clarifications are needed.

 

[dhookom]

It isn't clear what the relationships are. Can you provide the primary and foreign key information?

Duane
Hook'D on Access
MS Access MVP

 

[jeffshex]

Yep,
The PK ClientID from tblClientInfo is the FK in both tblProgramInfo and tblHours. Otherwise the primary keys for tblProgramInfo is ProgramID and tblHours is HoursID.
All of the PK fields are autonumber.

 

[dhookom]

So there can be multiple Program records per Client. A query with just the Client and Hours should work ok. How would you determine which Program record to display in your form?

I don't care much for Week1, Week2, ... unless this is a temporary data entry table that has values pushed to a normalized table.

Duane
Hook'D on Access
MS Access MVP

 

[jeffshex]

The program record would be determined by their ParticipationDate from the tblProgramInfo which would be asked for on the initial form that brings up the hours form.

I usually normalize out, but this is how the user wanted to input it as, he doesn't want to have to punch in a date for every week. He just wants to know what month and for each week of the month and that's it. I'm not sure how to get around that w/out having him enter data.

He will only by entering hour information every other week, so maybe two times a month tops.

 

[SkipVought]

but this is how the user wanted to input it as, he doesn't want to have to punch in a date for every week.

The user should never be entering data directly into a table. That's what FORMS are for!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jeffshex]

OK - But in my OP in the first few lines, you will see i'm trying to present the form in datasheet view which is tied to the problematic non-updateable query.

Nowhere did I even mention they enter it directly into tables.

I'm just curious on any approaches/techniques to solve the issue of the non-updateable query. I'm guessing I just need to do unbound fields and toss in some insert statements in vba, but since I haven't tried this way before, I thought that someone here may have done this before and be able to point me in the right direction.

 

[SkipVought]

he doesn't want to have to punch in a date for every week.

...implies the user is entering. Otherwise, the FORM can be use facilitate the next week's date: no date punching required.

This user's DATE ENTRY REQUIREMENT in no way dictates table design.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jeffshex]

It may or may not be the next weeks date. He doens't have a set time when he will enter in the dates, so automating that part is not a huge concern for me.

The base query (unupdateable), that is bound to my data sheet view form is:

SELECT tblClientInfo.ClientID, tblClientInfo.LName, tblClientInfo.FName, tblProgramInfo.ParticipationDate, tblProgramInfo.TotalAssignedHrs, tblHours.MonthDate, tblHours.Week1, tblHours.Week2, tblHours.Week3, tblHours.Week4, tblHours.Week5, tblHours.Edu_and_Training, tblHours.Fundraising, tblHours.Service_Hours
FROM (tblClientInfo LEFT JOIN tblHours ON tblClientInfo.ClientID=tblHours.ClientID) INNER JOIN tblProgramInfo ON tblClientInfo.ClientID=tblProgramInfo.ClientID;

Now, I have a selection form lets call "frmUserSelection" where they enter the date range for the participation date. That part will pull up the correct group.
Also, that selection form allows them to enter for what month/year.
This form will allow manipulation of the WHERE clause of the base query.

This will pull up the "frmEnterHours" data sheet view form.
If any data exists for that group/month/year selection, it will show, but it should allow for him to enter data for the selected fields in the tblHours that the query is grabbing.

Does that help any more?

 

[dhookom]

If you can't make one record source editable, I generally opt for more than one record source/row source. You can possibly use one or more subforms or list/combo boxes to display related information.

Duane
Hook'D on Access
MS Access MVP