K
knowshowrosegrows
Forgive my ignorance. I'm really very good at my actual job.
So my saga starts with a parameters form (as all good sagas do). The
frmDateProgramParam has a dropdown to choose a Prm_Code from
qryCensusFieldsAll. This param form also has a text box to type in and
choose a CensusDate from the same query. The SQL for qryCensusFieldsAll is:
SELECT TblAgency.Agency, tblCensusContactInfo.CensusPhone,
tblCensusContactInfo.CensusName, tbProgram.Cap, tblLOC.CareInt,
tblLOC.CareType, tblLOC.LOC_Alias, tbProgram.Program, tbProgram.Prm_Code,
tbProgram.Location
FROM tblLOC INNER JOIN (tblCensusContactInfo INNER JOIN (TblAgency INNER
JOIN tbProgram ON TblAgency.Prov_ID = tbProgram.Prov_ID) ON
tblCensusContactInfo.CensusContact_ID = tbProgram.CensusContact_ID) ON
tblLOC.LOC_Sort = tbProgram.LOC_Sort;
The frmCensusUpdate has code in the open event to take me to the param form,
I drop down and choose a Prm_Code and put a date in to choose the date of the
census event I want to update. Then I "should" be running the
qryCensusUpdate which is able to update the fields in the tblCensusEvent.
Here is the SQL for the qryCensusUpdate:
SELECT [Cap]-[Census]-[Admiss]+[D/C] AS Slots, tblCensusEvent.Census_ID,
tblCensusEvent.Prm_Code, tblCensusEvent.CensusDate, tblCensusEvent.Census,
tblCensusEvent.Admiss, tblCensusEvent.[D/C], qryCensusFieldsAll.Agency,
qryCensusFieldsAll.CensusPhone, qryCensusFieldsAll.CensusName,
qryCensusFieldsAll.CareInt, qryCensusFieldsAll.CareType,
qryCensusFieldsAll.LOC_Alias, qryCensusFieldsAll.Program,
qryCensusFieldsAll.Cap, qryCensusFieldsAll.Location
FROM tblCensusEvent LEFT JOIN qryCensusFieldsAll ON tblCensusEvent.Prm_Code
= qryCensusFieldsAll.Prm_Code
WHERE
(((tblCensusEvent.Prm_Code)=[forms]![frmDateProgramParam]![cmbChooseProgram])
AND ((tblCensusEvent.CensusDate)=[forms]![frmDateProgramParam]![DateChoice]))
ORDER BY tblCensusEvent.CensusDate DESC , qryCensusFieldsAll.Agency,
qryCensusFieldsAll.LOC_Alias;
I open the frmCensusUpdate and it takes me to the frmDateProgramParam where
I can enter a date and drop down and choose a Prm_Code. I then hit a cmd
button that takes me to my frmCensusUpdate but it does not have the record I
chose. It is blank and looks like it wants me to enter a new record.
Am I making this update form way too difficult? Have I made this discussion
group message way too confusing?
So my saga starts with a parameters form (as all good sagas do). The
frmDateProgramParam has a dropdown to choose a Prm_Code from
qryCensusFieldsAll. This param form also has a text box to type in and
choose a CensusDate from the same query. The SQL for qryCensusFieldsAll is:
SELECT TblAgency.Agency, tblCensusContactInfo.CensusPhone,
tblCensusContactInfo.CensusName, tbProgram.Cap, tblLOC.CareInt,
tblLOC.CareType, tblLOC.LOC_Alias, tbProgram.Program, tbProgram.Prm_Code,
tbProgram.Location
FROM tblLOC INNER JOIN (tblCensusContactInfo INNER JOIN (TblAgency INNER
JOIN tbProgram ON TblAgency.Prov_ID = tbProgram.Prov_ID) ON
tblCensusContactInfo.CensusContact_ID = tbProgram.CensusContact_ID) ON
tblLOC.LOC_Sort = tbProgram.LOC_Sort;
The frmCensusUpdate has code in the open event to take me to the param form,
I drop down and choose a Prm_Code and put a date in to choose the date of the
census event I want to update. Then I "should" be running the
qryCensusUpdate which is able to update the fields in the tblCensusEvent.
Here is the SQL for the qryCensusUpdate:
SELECT [Cap]-[Census]-[Admiss]+[D/C] AS Slots, tblCensusEvent.Census_ID,
tblCensusEvent.Prm_Code, tblCensusEvent.CensusDate, tblCensusEvent.Census,
tblCensusEvent.Admiss, tblCensusEvent.[D/C], qryCensusFieldsAll.Agency,
qryCensusFieldsAll.CensusPhone, qryCensusFieldsAll.CensusName,
qryCensusFieldsAll.CareInt, qryCensusFieldsAll.CareType,
qryCensusFieldsAll.LOC_Alias, qryCensusFieldsAll.Program,
qryCensusFieldsAll.Cap, qryCensusFieldsAll.Location
FROM tblCensusEvent LEFT JOIN qryCensusFieldsAll ON tblCensusEvent.Prm_Code
= qryCensusFieldsAll.Prm_Code
WHERE
(((tblCensusEvent.Prm_Code)=[forms]![frmDateProgramParam]![cmbChooseProgram])
AND ((tblCensusEvent.CensusDate)=[forms]![frmDateProgramParam]![DateChoice]))
ORDER BY tblCensusEvent.CensusDate DESC , qryCensusFieldsAll.Agency,
qryCensusFieldsAll.LOC_Alias;
I open the frmCensusUpdate and it takes me to the frmDateProgramParam where
I can enter a date and drop down and choose a Prm_Code. I then hit a cmd
button that takes me to my frmCensusUpdate but it does not have the record I
chose. It is blank and looks like it wants me to enter a new record.
Am I making this update form way too difficult? Have I made this discussion
group message way too confusing?