Input box not showing up.

[S]

I have created a button to update a field. I am looking for it to show an
input box. I will then input the Day (Friday, Saturday or Sunday)

My problem is the input box is not showing up.
Here is my code.

Private Sub Command7_Click()
Set rec_ses = CurrentDb.OpenRecordset("select ceremony from entries group by
ceremony order by ceremony", dbOpenDynaset)
Do While Not rec_ses.EOF
'you specify the day for the ceremony in the input box
Day = InputBox("Specify the Day for Session " & rec_ses!Ceremony)
'this recordset contains all the entries in the current ceremony ordered
by entries
Set rec = CurrentDb.OpenRecordset("select * from entries where
ceremony=""" & rec_ses!Ceremony & """ order by [entry #]", dbOpenDynaset)
num_entry = 0
Do While Not rec.EOF
rec.Edit
rec!Day = rec!Day
rec.Update
rec.MoveNext
num_entry = num_entry + 1
Loop
rec.Close
'you go to next ceremony
rec_ses.MoveNext
Loop
rec_ses.Close
MsgBox "Update completed"

End Sub

Any help is greatly appreciated.

 

[Douglas J. Steele]

Event procedures sometimes get separated from the controls to which they
apply. Go into the Properties window for the command button, and make sure
that the OnClick property has [Event Procedure] as its value.

Incidentally, Day is a reserved word, so you should rename your variable.
For a comprehensive list of names to avoid, see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

As well, there's an error in your code.

rec!Day = rec!Day

doesn't make sense. That's just going to set the field to its current value.
However, it also doesn't make sense to loop through a recordset to do that
either. Use an Update query instead.

 

[S]

Thank you for the response.

I will rename the "Day" field.

I orignally did the update query. However I needed 5 update queries. one for
ceremony 1, ceremony 2, ceremony 3, ceremony 4, ceremony 5.

I then created a macro to run all 5 update queries.

My issue was that sometimes we use all 5 ceremonies and sometimes we don't.
So when i run the macro, i only wanted it to update the ceremony's that have
records in them. Leaving it the way i had it, It would ask for a "Day" for
all 5 ceremony's to update even though we may not be using Ceremony 4 and 5.

Is it possible to have the update Query find just ceremony's that have
records in them and update each ceremony with a "Day" that I input?

Event procedures sometimes get separated from the controls to which they
apply. Go into the Properties window for the command button, and make sure
that the OnClick property has [Event Procedure] as its value.

Incidentally, Day is a reserved word, so you should rename your variable.
For a comprehensive list of names to avoid, see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

As well, there's an error in your code.

rec!Day = rec!Day

doesn't make sense. That's just going to set the field to its current value.
However, it also doesn't make sense to loop through a recordset to do that
either. Use an Update query instead.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have created a button to update a field. I am looking for it to show an
input box. I will then input the Day (Friday, Saturday or Sunday)

My problem is the input box is not showing up.
Here is my code.

Private Sub Command7_Click()
Set rec_ses = CurrentDb.OpenRecordset("select ceremony from entries group
by
ceremony order by ceremony", dbOpenDynaset)
Do While Not rec_ses.EOF
'you specify the day for the ceremony in the input box
Day = InputBox("Specify the Day for Session " & rec_ses!Ceremony)
'this recordset contains all the entries in the current ceremony
ordered
by entries
Set rec = CurrentDb.OpenRecordset("select * from entries where
ceremony=""" & rec_ses!Ceremony & """ order by [entry #]", dbOpenDynaset)
num_entry = 0
Do While Not rec.EOF
rec.Edit
rec!Day = rec!Day
rec.Update
rec.MoveNext
num_entry = num_entry + 1
Loop
rec.Close
'you go to next ceremony
rec_ses.MoveNext
Loop
rec_ses.Close
MsgBox "Update completed"

End Sub

Any help is greatly appreciated.

 

[Douglas J. Steele]

You can still have recordset rec_ses. It was the rec recordset that I felt
was unnecessary.

Since I wasn't sure what the Day field was supposed to be, I couldn't
provide you with replacement code.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thank you for the response.

I will rename the "Day" field.

I orignally did the update query. However I needed 5 update queries. one
for
ceremony 1, ceremony 2, ceremony 3, ceremony 4, ceremony 5.

I then created a macro to run all 5 update queries.

My issue was that sometimes we use all 5 ceremonies and sometimes we
don't.
So when i run the macro, i only wanted it to update the ceremony's that
have
records in them. Leaving it the way i had it, It would ask for a "Day" for
all 5 ceremony's to update even though we may not be using Ceremony 4 and
5.

Is it possible to have the update Query find just ceremony's that have
records in them and update each ceremony with a "Day" that I input?

Event procedures sometimes get separated from the controls to which they
apply. Go into the Properties window for the command button, and make
sure
that the OnClick property has [Event Procedure] as its value.

Incidentally, Day is a reserved word, so you should rename your variable.
For a comprehensive list of names to avoid, see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

As well, there's an error in your code.

rec!Day = rec!Day

doesn't make sense. That's just going to set the field to its current
value.
However, it also doesn't make sense to loop through a recordset to do
that
either. Use an Update query instead.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have created a button to update a field. I am looking for it to show
an
input box. I will then input the Day (Friday, Saturday or Sunday)

My problem is the input box is not showing up.
Here is my code.

Private Sub Command7_Click()
Set rec_ses = CurrentDb.OpenRecordset("select ceremony from entries
group
by
ceremony order by ceremony", dbOpenDynaset)
Do While Not rec_ses.EOF
'you specify the day for the ceremony in the input box
Day = InputBox("Specify the Day for Session " & rec_ses!Ceremony)
'this recordset contains all the entries in the current ceremony
ordered
by entries
Set rec = CurrentDb.OpenRecordset("select * from entries where
ceremony=""" & rec_ses!Ceremony & """ order by [entry #]",
dbOpenDynaset)
num_entry = 0
Do While Not rec.EOF
rec.Edit
rec!Day = rec!Day
rec.Update
rec.MoveNext
num_entry = num_entry + 1
Loop
rec.Close
'you go to next ceremony
rec_ses.MoveNext
Loop
rec_ses.Close
MsgBox "Update completed"

End Sub

Any help is greatly appreciated.