Probably an Obvious Concept

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?
 
S

Steve Sanford

What is the row source for the combo box "[cmbChooseProgram]"?

And what is the code for the button that takes you to the form
"frmCensusUpdat" from the form "frmDateProgramParam"?

----
OK, now for some troubleshooting...

First, *make a copy of your databse*!!

Since I can't see your database, I have to ask questions....bear with me.

If you run the query "qryCensusFieldsAll", are records returned?

If yes, then close that query and open the query "qryCensusUpdate" in design
view.
Remove any criteria. Run the query. Are records returned?

If yes, then goto design view and enter a date in the criteria for the field
"[CensusDate]" that is in the table. Don't forget to delimit the date with
the '#' signs (ex. #11/13/2007#). Now run the query. Are records returned?

If yes, then return to design view, remove the date criteria and enter a
criteria for the field "Prm_Code". If it is text, delimit it with quotes.
Run the query. Are records returned?

If at any point records are not returned, the query(s) need(s) to be fixed.

---
So what you have/want is a search form.
Open the form "frmCensusUpdate" in design view.
In the form header, copy the combo box and text box from the form
"frmDateProgramParam" and paste them in the header.
Make sure the names are still "cmbChooseProgram" for the combo box and
"DateChoice" for the text box.

Now modify the query "qryCensusUpdate".
Open the query in design view.
In the criteria row for the field "[Prm_Code]", enter:
[forms]![frmDateProgramParam]![cmbChooseProgram] .

In the criteria row for the field "[CensusDate]", enter:
[forms]![frmDateProgramParam]![DateChoice]

Add a button to the form header. In the click event, enter:
Me.Requery

Save the form, then open it.
Select something in the combo box and enter a date in the text box.
Click the button to requery the form record source.

Now the only problem *might* be that the query "qryCensusUpdate" is not
updateable. :)

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


knowshowrosegrows said:
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?
Click to expand...
 
K

knowshowrosegrows

Thanks for your thorough reply.

Yes, all the queries return records.

The row source for cmbChooseProgram is SELECT qryCensusFieldsAll.Prm_Code,
qryCensusFieldsAll.Agency, qryCensusFieldsAll.LOC_Alias,
qryCensusFieldsAll.Location, qryCensusFieldsAll.Prm_Code FROM
qryCensusFieldsAll ORDER BY Agency;

So I put a copy of the combo box and text box in the header of
frmCensusUpdate. Is that where I put the button with the code me.requery?

The criteria [forms]![frmDateProgramParam]![cmbChooseProgram] is already in
the [Prm_Code] criteria on the qryCensusUpdate. The criteria
[forms]![frmDateProgramParam]![DateChoice] is already in the [Census Date]
criteria.

The code
Private Sub cmdDateChoice_Click()
Me.Visible = False
End Sub
is behind the button in the frmDateProgramParam that sends me to the
frmCensusUpdate.

Regardless, when I choose a program and date in either of the two places I
can now choose (the frmProgramParam or the header of the frm CensusUpdate)
and then hit the requeury button (in the header of frmCensusUpdate), I am not
getting any records in the text boxes that represent those records in the
frmCensusUpdate.
--
Thanks


Steve Sanford said:
What is the row source for the combo box "[cmbChooseProgram]"?

And what is the code for the button that takes you to the form
"frmCensusUpdat" from the form "frmDateProgramParam"?

----
OK, now for some troubleshooting...

First, *make a copy of your databse*!!

Since I can't see your database, I have to ask questions....bear with me.

If you run the query "qryCensusFieldsAll", are records returned?

If yes, then close that query and open the query "qryCensusUpdate" in design
view.
Remove any criteria. Run the query. Are records returned?

If yes, then goto design view and enter a date in the criteria for the field
"[CensusDate]" that is in the table. Don't forget to delimit the date with
the '#' signs (ex. #11/13/2007#). Now run the query. Are records returned?

If yes, then return to design view, remove the date criteria and enter a
criteria for the field "Prm_Code". If it is text, delimit it with quotes.
Run the query. Are records returned?

If at any point records are not returned, the query(s) need(s) to be fixed.

---
So what you have/want is a search form.
Open the form "frmCensusUpdate" in design view.
In the form header, copy the combo box and text box from the form
"frmDateProgramParam" and paste them in the header.
Make sure the names are still "cmbChooseProgram" for the combo box and
"DateChoice" for the text box.

Now modify the query "qryCensusUpdate".
Open the query in design view.
In the criteria row for the field "[Prm_Code]", enter:
[forms]![frmDateProgramParam]![cmbChooseProgram] .

In the criteria row for the field "[CensusDate]", enter:
[forms]![frmDateProgramParam]![DateChoice]

Add a button to the form header. In the click event, enter:
Me.Requery

Save the form, then open it.
Select something in the combo box and enter a date in the text box.
Click the button to requery the form record source.

Now the only problem *might* be that the query "qryCensusUpdate" is not
updateable. :)

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


knowshowrosegrows said:
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?
Click to expand...
Click to expand...
 
S

Steve Sanford

Sorry I was away for so long... family illness and holidays.


OK, so the combo box and the text box are in the header of the form
"frmCensusUpdate". Yes, the header is where the button to requery the form
should be.

Don't forget to change the criteria for the query "qryCensusUpdate" - the
criteria should reference "frmCensusUpdate", not "frmDateProgramParameter".

The criteria row for the field "[Prm_Code]" should be:
[forms]![frmCensusUpdate]![cmbChooseProgram] .

The criteria row for the field "[CensusDate]" should be:
[forms]![frmCensusUpdate]![DateChoice]


Enter both criteria in the header and click on the button. The detail
section will show the records returned.

---
After making the changes above, open the form "frmCensusUpdate" and enter
data in the combo box and the text box. Then, in the database window, goto
QUERIES and open the query "qryCensusUpdate". The form "frmCensusUpdate" must
be open. If there are records that meet the criteria, they will be displayed.
Close the query and click the button on the form "frmCensusUpdate".

BTW, the form "frmCensusUpdate" *is* set to continous forms and the DATA
ENTRY property is set to NO???



HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


knowshowrosegrows said:
Thanks for your thorough reply.

Yes, all the queries return records.

The row source for cmbChooseProgram is SELECT qryCensusFieldsAll.Prm_Code,
qryCensusFieldsAll.Agency, qryCensusFieldsAll.LOC_Alias,
qryCensusFieldsAll.Location, qryCensusFieldsAll.Prm_Code FROM
qryCensusFieldsAll ORDER BY Agency;

So I put a copy of the combo box and text box in the header of
frmCensusUpdate. Is that where I put the button with the code me.requery?

The criteria [forms]![frmDateProgramParam]![cmbChooseProgram] is already in
the [Prm_Code] criteria on the qryCensusUpdate. The criteria
[forms]![frmDateProgramParam]![DateChoice] is already in the [Census Date]
criteria.

The code
Private Sub cmdDateChoice_Click()
Me.Visible = False
End Sub
is behind the button in the frmDateProgramParam that sends me to the
frmCensusUpdate.

Regardless, when I choose a program and date in either of the two places I
can now choose (the frmProgramParam or the header of the frm CensusUpdate)
and then hit the requeury button (in the header of frmCensusUpdate), I am not
getting any records in the text boxes that represent those records in the
frmCensusUpdate.
--
Thanks


Steve Sanford said:
What is the row source for the combo box "[cmbChooseProgram]"?

And what is the code for the button that takes you to the form
"frmCensusUpdat" from the form "frmDateProgramParam"?

----
OK, now for some troubleshooting...

First, *make a copy of your databse*!!

Since I can't see your database, I have to ask questions....bear with me.

If you run the query "qryCensusFieldsAll", are records returned?

If yes, then close that query and open the query "qryCensusUpdate" in design
view.
Remove any criteria. Run the query. Are records returned?

If yes, then goto design view and enter a date in the criteria for the field
"[CensusDate]" that is in the table. Don't forget to delimit the date with
the '#' signs (ex. #11/13/2007#). Now run the query. Are records returned?

If yes, then return to design view, remove the date criteria and enter a
criteria for the field "Prm_Code". If it is text, delimit it with quotes.
Run the query. Are records returned?

If at any point records are not returned, the query(s) need(s) to be fixed.

---
So what you have/want is a search form.
Open the form "frmCensusUpdate" in design view.
In the form header, copy the combo box and text box from the form
"frmDateProgramParam" and paste them in the header.
Make sure the names are still "cmbChooseProgram" for the combo box and
"DateChoice" for the text box.

Now modify the query "qryCensusUpdate".
Open the query in design view.
In the criteria row for the field "[Prm_Code]", enter:
[forms]![frmDateProgramParam]![cmbChooseProgram] .

In the criteria row for the field "[CensusDate]", enter:
[forms]![frmDateProgramParam]![DateChoice]

Add a button to the form header. In the click event, enter:
Me.Requery

Save the form, then open it.
Select something in the combo box and enter a date in the text box.
Click the button to requery the form record source.

Now the only problem *might* be that the query "qryCensusUpdate" is not
updateable. :)

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


knowshowrosegrows said:
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?
Click to expand...
Click to expand...
Click to expand...
 
K

knowshowrosegrows

You are the best!! That works just right. Thanks - you taught me a lot.
--
Thanks


Steve Sanford said:
Sorry I was away for so long... family illness and holidays.


OK, so the combo box and the text box are in the header of the form
"frmCensusUpdate". Yes, the header is where the button to requery the form
should be.

Don't forget to change the criteria for the query "qryCensusUpdate" - the
criteria should reference "frmCensusUpdate", not "frmDateProgramParameter".

The criteria row for the field "[Prm_Code]" should be:
[forms]![frmCensusUpdate]![cmbChooseProgram] .

The criteria row for the field "[CensusDate]" should be:
[forms]![frmCensusUpdate]![DateChoice]


Enter both criteria in the header and click on the button. The detail
section will show the records returned.

---
After making the changes above, open the form "frmCensusUpdate" and enter
data in the combo box and the text box. Then, in the database window, goto
QUERIES and open the query "qryCensusUpdate". The form "frmCensusUpdate" must
be open. If there are records that meet the criteria, they will be displayed.
Close the query and click the button on the form "frmCensusUpdate".

BTW, the form "frmCensusUpdate" *is* set to continous forms and the DATA
ENTRY property is set to NO???



HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


knowshowrosegrows said:
Thanks for your thorough reply.

Yes, all the queries return records.

The row source for cmbChooseProgram is SELECT qryCensusFieldsAll.Prm_Code,
qryCensusFieldsAll.Agency, qryCensusFieldsAll.LOC_Alias,
qryCensusFieldsAll.Location, qryCensusFieldsAll.Prm_Code FROM
qryCensusFieldsAll ORDER BY Agency;

So I put a copy of the combo box and text box in the header of
frmCensusUpdate. Is that where I put the button with the code me.requery?

The criteria [forms]![frmDateProgramParam]![cmbChooseProgram] is already in
the [Prm_Code] criteria on the qryCensusUpdate. The criteria
[forms]![frmDateProgramParam]![DateChoice] is already in the [Census Date]
criteria.

The code
Private Sub cmdDateChoice_Click()
Me.Visible = False
End Sub
is behind the button in the frmDateProgramParam that sends me to the
frmCensusUpdate.

Regardless, when I choose a program and date in either of the two places I
can now choose (the frmProgramParam or the header of the frm CensusUpdate)
and then hit the requeury button (in the header of frmCensusUpdate), I am not
getting any records in the text boxes that represent those records in the
frmCensusUpdate.
--
Thanks


Steve Sanford said:
What is the row source for the combo box "[cmbChooseProgram]"?

And what is the code for the button that takes you to the form
"frmCensusUpdat" from the form "frmDateProgramParam"?

----
OK, now for some troubleshooting...

First, *make a copy of your databse*!!

Since I can't see your database, I have to ask questions....bear with me.

If you run the query "qryCensusFieldsAll", are records returned?

If yes, then close that query and open the query "qryCensusUpdate" in design
view.
Remove any criteria. Run the query. Are records returned?

If yes, then goto design view and enter a date in the criteria for the field
"[CensusDate]" that is in the table. Don't forget to delimit the date with
the '#' signs (ex. #11/13/2007#). Now run the query. Are records returned?

If yes, then return to design view, remove the date criteria and enter a
criteria for the field "Prm_Code". If it is text, delimit it with quotes.
Run the query. Are records returned?

If at any point records are not returned, the query(s) need(s) to be fixed.

---
So what you have/want is a search form.
Open the form "frmCensusUpdate" in design view.
In the form header, copy the combo box and text box from the form
"frmDateProgramParam" and paste them in the header.
Make sure the names are still "cmbChooseProgram" for the combo box and
"DateChoice" for the text box.

Now modify the query "qryCensusUpdate".
Open the query in design view.
In the criteria row for the field "[Prm_Code]", enter:
[forms]![frmDateProgramParam]![cmbChooseProgram] .

In the criteria row for the field "[CensusDate]", enter:
[forms]![frmDateProgramParam]![DateChoice]

Add a button to the form header. In the click event, enter:
Me.Requery

Save the form, then open it.
Select something in the combo box and enter a date in the text box.
Click the button to requery the form record source.

Now the only problem *might* be that the query "qryCensusUpdate" is not
updateable. :)

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

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?
Click to expand...
Click to expand...
Click to expand...
 
S

Steve Sanford

Wonderful. Glad you got it working..

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


knowshowrosegrows said:
You are the best!! That works just right. Thanks - you taught me a lot.
--
Thanks


Steve Sanford said:
Sorry I was away for so long... family illness and holidays.


OK, so the combo box and the text box are in the header of the form
"frmCensusUpdate". Yes, the header is where the button to requery the form
should be.

Don't forget to change the criteria for the query "qryCensusUpdate" - the
criteria should reference "frmCensusUpdate", not "frmDateProgramParameter".

The criteria row for the field "[Prm_Code]" should be:
[forms]![frmCensusUpdate]![cmbChooseProgram] .

The criteria row for the field "[CensusDate]" should be:
[forms]![frmCensusUpdate]![DateChoice]


Enter both criteria in the header and click on the button. The detail
section will show the records returned.

---
After making the changes above, open the form "frmCensusUpdate" and enter
data in the combo box and the text box. Then, in the database window, goto
QUERIES and open the query "qryCensusUpdate". The form "frmCensusUpdate" must
be open. If there are records that meet the criteria, they will be displayed.
Close the query and click the button on the form "frmCensusUpdate".

BTW, the form "frmCensusUpdate" *is* set to continous forms and the DATA
ENTRY property is set to NO???



HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


knowshowrosegrows said:
Thanks for your thorough reply.

Yes, all the queries return records.

The row source for cmbChooseProgram is SELECT qryCensusFieldsAll.Prm_Code,
qryCensusFieldsAll.Agency, qryCensusFieldsAll.LOC_Alias,
qryCensusFieldsAll.Location, qryCensusFieldsAll.Prm_Code FROM
qryCensusFieldsAll ORDER BY Agency;

So I put a copy of the combo box and text box in the header of
frmCensusUpdate. Is that where I put the button with the code me.requery?

The criteria [forms]![frmDateProgramParam]![cmbChooseProgram] is already in
the [Prm_Code] criteria on the qryCensusUpdate. The criteria
[forms]![frmDateProgramParam]![DateChoice] is already in the [Census Date]
criteria.

The code
Private Sub cmdDateChoice_Click()
Me.Visible = False
End Sub
is behind the button in the frmDateProgramParam that sends me to the
frmCensusUpdate.

Regardless, when I choose a program and date in either of the two places I
can now choose (the frmProgramParam or the header of the frm CensusUpdate)
and then hit the requeury button (in the header of frmCensusUpdate), I am not
getting any records in the text boxes that represent those records in the
frmCensusUpdate.
--
Thanks


:

What is the row source for the combo box "[cmbChooseProgram]"?

And what is the code for the button that takes you to the form
"frmCensusUpdat" from the form "frmDateProgramParam"?

----
OK, now for some troubleshooting...

First, *make a copy of your databse*!!

Since I can't see your database, I have to ask questions....bear with me.

If you run the query "qryCensusFieldsAll", are records returned?

If yes, then close that query and open the query "qryCensusUpdate" in design
view.
Remove any criteria. Run the query. Are records returned?

If yes, then goto design view and enter a date in the criteria for the field
"[CensusDate]" that is in the table. Don't forget to delimit the date with
the '#' signs (ex. #11/13/2007#). Now run the query. Are records returned?

If yes, then return to design view, remove the date criteria and enter a
criteria for the field "Prm_Code". If it is text, delimit it with quotes.
Run the query. Are records returned?

If at any point records are not returned, the query(s) need(s) to be fixed.

---
So what you have/want is a search form.
Open the form "frmCensusUpdate" in design view.
In the form header, copy the combo box and text box from the form
"frmDateProgramParam" and paste them in the header.
Make sure the names are still "cmbChooseProgram" for the combo box and
"DateChoice" for the text box.

Now modify the query "qryCensusUpdate".
Open the query in design view.
In the criteria row for the field "[Prm_Code]", enter:
[forms]![frmDateProgramParam]![cmbChooseProgram] .

In the criteria row for the field "[CensusDate]", enter:
[forms]![frmDateProgramParam]![DateChoice]

Add a button to the form header. In the click event, enter:
Me.Requery

Save the form, then open it.
Select something in the combo box and enter a date in the text box.
Click the button to requery the form record source.

Now the only problem *might* be that the query "qryCensusUpdate" is not
updateable. :)

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

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?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Date Parameters Irritation 5

Top