Form code not behaving as expected

G

Guest

2 issues, one is a coding issue and one I think I may be able to fix with code.

1. I have a form (frmContact) where users enter time sheet data. Time is
broken down into 7 different categories and into .5 hour increments within
those categories. Each category is displayed as a list box. When a user
enters time into the list boxes, the time they chose is highlighted. If they
go on to a new record, and then back to the previous record to view or edit
it, the time they chose is no longer highlighted, so they have no way of
knowing the time that is being held in that record. That isn't true all the
time though. For example, occasionally the time they chose in certain
categories will still be highlighted, while the time chosen in the list box
next to it is not highlighted. The record is definately being saved (I
checked the table to be sure) What's going on there? It appears to be a
fluke with no real rhyme or reason. Is there a way to code it so that the
time they chose is always highlighted or is there a specific reason behind
this that has an easier fix?

2. The same form (frmContact) has a label (being used as a command button)
on it that has the following code behind it:

Private Sub Label43_Click()
Dim stLinkCriteria As String

If IsNull(dtmDate) Then
Me.dtmDate.BackColor = 255
If MsgBox("The date field is null. Closing this form without a date
will delete this record." & vbNewLine & "If you wish to proceed click OK." &
vbNewLine & "If you wish to enter a date, Click Cancel.", vbOKCancel,
"WARNING!") = vbCancel Then
Cancel = True
Me.dtmDate.SetFocus

Else: DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
stLinkCriteria = "[ClientCaseInfoId]= " & Me![ClientCaseInfoID]
DoCmd.OpenForm "frmSummary", , , stLinkCriteria, , , ClientCaseInfoID

End If
End If

End Sub

The scenario I envisioned for the command button was that there would be
times when there would be no time and no date entered in the list boxes on
frmContact and it would be used simply as a way to pass the ClientCaseInfoID
into frmSummary. At other times there WOULD be time and a date listed on
frmContact, so that the current record would be saved and THEN the
ClientCaseInfoID would be passed into frmSummary. When a date is not
entered, the code works fine. But, if there is a date entered, the event
behind the label doesn't seem to fire. In other words, frmSummary does not
open. I HAVE to be in a record that has no data (other than the
ClientCaseInfoID) entered in order for frmSummary to open when I click the
label.

Any help is appreciated!
 
G

Guest

First, are you sure you are using a List Box control for time entry? It
can't be, you can't enter data in a List box. You can only select it. So
either your terminology is incorrect or you are choosing the time by clicking
on a list of times. This doesn't seem like a reasonable thing to do, so I
suspect I am not fully understanding what you say.

Next, why are you using a Label as a command button? In your code, you set
Cancel = True. There is No Cancel argument in a Click event, so you can't
cancel it. Also, the way the code is written. The only time anything happens
is if the dtmDate is Null.

It is also unclear how the data gets from the list box to the dtmDate text
box.
--
Dave Hargis, Microsoft Access MVP


Tara said:
2 issues, one is a coding issue and one I think I may be able to fix with code.

1. I have a form (frmContact) where users enter time sheet data. Time is
broken down into 7 different categories and into .5 hour increments within
those categories. Each category is displayed as a list box. When a user
enters time into the list boxes, the time they chose is highlighted. If they
go on to a new record, and then back to the previous record to view or edit
it, the time they chose is no longer highlighted, so they have no way of
knowing the time that is being held in that record. That isn't true all the
time though. For example, occasionally the time they chose in certain
categories will still be highlighted, while the time chosen in the list box
next to it is not highlighted. The record is definately being saved (I
checked the table to be sure) What's going on there? It appears to be a
fluke with no real rhyme or reason. Is there a way to code it so that the
time they chose is always highlighted or is there a specific reason behind
this that has an easier fix?

2. The same form (frmContact) has a label (being used as a command button)
on it that has the following code behind it:

Private Sub Label43_Click()
Dim stLinkCriteria As String

If IsNull(dtmDate) Then
Me.dtmDate.BackColor = 255
If MsgBox("The date field is null. Closing this form without a date
will delete this record." & vbNewLine & "If you wish to proceed click OK." &
vbNewLine & "If you wish to enter a date, Click Cancel.", vbOKCancel,
"WARNING!") = vbCancel Then
Cancel = True
Me.dtmDate.SetFocus

Else: DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
stLinkCriteria = "[ClientCaseInfoId]= " & Me![ClientCaseInfoID]
DoCmd.OpenForm "frmSummary", , , stLinkCriteria, , , ClientCaseInfoID

End If
End If

End Sub

The scenario I envisioned for the command button was that there would be
times when there would be no time and no date entered in the list boxes on
frmContact and it would be used simply as a way to pass the ClientCaseInfoID
into frmSummary. At other times there WOULD be time and a date listed on
frmContact, so that the current record would be saved and THEN the
ClientCaseInfoID would be passed into frmSummary. When a date is not
entered, the code works fine. But, if there is a date entered, the event
behind the label doesn't seem to fire. In other words, frmSummary does not
open. I HAVE to be in a record that has no data (other than the
ClientCaseInfoID) entered in order for frmSummary to open when I click the
label.

Any help is appreciated!
Click to expand...
 
C

Carl Rapson

Tara said:
2 issues, one is a coding issue and one I think I may be able to fix with
code.

1. I have a form (frmContact) where users enter time sheet data. Time is
broken down into 7 different categories and into .5 hour increments within
those categories. Each category is displayed as a list box. When a user
enters time into the list boxes, the time they chose is highlighted. If
they
go on to a new record, and then back to the previous record to view or
edit
it, the time they chose is no longer highlighted, so they have no way of
knowing the time that is being held in that record. That isn't true all
the
time though. For example, occasionally the time they chose in certain
categories will still be highlighted, while the time chosen in the list
box
next to it is not highlighted. The record is definately being saved (I
checked the table to be sure) What's going on there? It appears to be a
fluke with no real rhyme or reason. Is there a way to code it so that the
time they chose is always highlighted or is there a specific reason behind
this that has an easier fix?

2. The same form (frmContact) has a label (being used as a command
button)
on it that has the following code behind it:

Private Sub Label43_Click()
Dim stLinkCriteria As String

If IsNull(dtmDate) Then
Me.dtmDate.BackColor = 255
If MsgBox("The date field is null. Closing this form without a
date
will delete this record." & vbNewLine & "If you wish to proceed click OK."
&
vbNewLine & "If you wish to enter a date, Click Cancel.", vbOKCancel,
"WARNING!") = vbCancel Then
Cancel = True
Me.dtmDate.SetFocus

Else: DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
stLinkCriteria = "[ClientCaseInfoId]= " & Me![ClientCaseInfoID]
DoCmd.OpenForm "frmSummary", , , stLinkCriteria, , , ClientCaseInfoID

End If
End If

End Sub

The scenario I envisioned for the command button was that there would be
times when there would be no time and no date entered in the list boxes on
frmContact and it would be used simply as a way to pass the
ClientCaseInfoID
into frmSummary. At other times there WOULD be time and a date listed on
frmContact, so that the current record would be saved and THEN the
ClientCaseInfoID would be passed into frmSummary. When a date is not
entered, the code works fine. But, if there is a date entered, the event
behind the label doesn't seem to fire. In other words, frmSummary does
not
open. I HAVE to be in a record that has no data (other than the
ClientCaseInfoID) entered in order for frmSummary to open when I click the
label.

Any help is appreciated!
Click to expand...

I'm not certain that I understand your form design completely from your
description, but it sounds like you might need to put some code into the
form's Current event that positions the listbox selections to the
appropriate values.

As for the second issue, you have the statement

If IsNull(dtmDate) Then...

at the beginning of your event. I suspect that the event is firing, but for
records that have a date entered the body of the event is being skipped. You
can test this by placing a breakpoint on the If IsNull... statement and see
if the code stops there.

I think this is more like you would want:

Private Sub Label43_Click()
Dim stLinkCriteria As String

If IsNull(dtmDate) Then
Me.dtmDate.BackColor = 255
If MsgBox("The date field is null. Closing this form without a date
will delete this record." & vbNewLine & "If you wish to proceed click OK." &
vbNewLine & "If you wish to enter a date, Click Cancel.", vbOKCancel,
"WARNING!") = vbCancel Then
Cancel = True
Me.dtmDate.SetFocus
Exit Sub
End If
End If
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
stLinkCriteria = "[ClientCaseInfoId]= " & Me![ClientCaseInfoID]
DoCmd.OpenForm "frmSummary", , , stLinkCriteria, , , ClientCaseInfoID
End Sub

Carl Rapson
 
G

Guest

Sorry, yes I used the wrong terminology. Users select time from the
listboxes, they do not "enter" it. I agree using a listbox is not the best
approach to recording time, however, the supervisor whose staff uses the
database insisted on it being this way, mainly because this is what they were
accustomed to and she didn't want it changed. As for the Label being used as
a command button, I was looking for flexibility in design. I wasn't aware
there was an issue in doing that. Is there? I understand your point about
the Cancel and will fix it. As for the data getting from the list box to the
dtmDate field, it doesn't. Not sure where I implied that, but I'm sorry if I
did.

Klatuu said:
First, are you sure you are using a List Box control for time entry? It
can't be, you can't enter data in a List box. You can only select it. So
either your terminology is incorrect or you are choosing the time by clicking
on a list of times. This doesn't seem like a reasonable thing to do, so I
suspect I am not fully understanding what you say.

Next, why are you using a Label as a command button? In your code, you set
Cancel = True. There is No Cancel argument in a Click event, so you can't
cancel it. Also, the way the code is written. The only time anything happens
is if the dtmDate is Null.

It is also unclear how the data gets from the list box to the dtmDate text
box.
--
Dave Hargis, Microsoft Access MVP


Tara said:
2 issues, one is a coding issue and one I think I may be able to fix with code.

1. I have a form (frmContact) where users enter time sheet data. Time is
broken down into 7 different categories and into .5 hour increments within
those categories. Each category is displayed as a list box. When a user
enters time into the list boxes, the time they chose is highlighted. If they
go on to a new record, and then back to the previous record to view or edit
it, the time they chose is no longer highlighted, so they have no way of
knowing the time that is being held in that record. That isn't true all the
time though. For example, occasionally the time they chose in certain
categories will still be highlighted, while the time chosen in the list box
next to it is not highlighted. The record is definately being saved (I
checked the table to be sure) What's going on there? It appears to be a
fluke with no real rhyme or reason. Is there a way to code it so that the
time they chose is always highlighted or is there a specific reason behind
this that has an easier fix?

2. The same form (frmContact) has a label (being used as a command button)
on it that has the following code behind it:

Private Sub Label43_Click()
Dim stLinkCriteria As String

If IsNull(dtmDate) Then
Me.dtmDate.BackColor = 255
If MsgBox("The date field is null. Closing this form without a date
will delete this record." & vbNewLine & "If you wish to proceed click OK." &
vbNewLine & "If you wish to enter a date, Click Cancel.", vbOKCancel,
"WARNING!") = vbCancel Then
Cancel = True
Me.dtmDate.SetFocus

Else: DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
stLinkCriteria = "[ClientCaseInfoId]= " & Me![ClientCaseInfoID]
DoCmd.OpenForm "frmSummary", , , stLinkCriteria, , , ClientCaseInfoID

End If
End If

End Sub

The scenario I envisioned for the command button was that there would be
times when there would be no time and no date entered in the list boxes on
frmContact and it would be used simply as a way to pass the ClientCaseInfoID
into frmSummary. At other times there WOULD be time and a date listed on
frmContact, so that the current record would be saved and THEN the
ClientCaseInfoID would be passed into frmSummary. When a date is not
entered, the code works fine. But, if there is a date entered, the event
behind the label doesn't seem to fire. In other words, frmSummary does not
open. I HAVE to be in a record that has no data (other than the
ClientCaseInfoID) entered in order for frmSummary to open when I click the
label.

Any help is appreciated!
Click to expand...
Click to expand...
 
G

Guest

Thanks for you input Carl, I appreciate it. I'll try your suggestions and
see how it comes out!

Carl Rapson said:
Tara said:
2 issues, one is a coding issue and one I think I may be able to fix with
code.

1. I have a form (frmContact) where users enter time sheet data. Time is
broken down into 7 different categories and into .5 hour increments within
those categories. Each category is displayed as a list box. When a user
enters time into the list boxes, the time they chose is highlighted. If
they
go on to a new record, and then back to the previous record to view or
edit
it, the time they chose is no longer highlighted, so they have no way of
knowing the time that is being held in that record. That isn't true all
the
time though. For example, occasionally the time they chose in certain
categories will still be highlighted, while the time chosen in the list
box
next to it is not highlighted. The record is definately being saved (I
checked the table to be sure) What's going on there? It appears to be a
fluke with no real rhyme or reason. Is there a way to code it so that the
time they chose is always highlighted or is there a specific reason behind
this that has an easier fix?

2. The same form (frmContact) has a label (being used as a command
button)
on it that has the following code behind it:

Private Sub Label43_Click()
Dim stLinkCriteria As String

If IsNull(dtmDate) Then
Me.dtmDate.BackColor = 255
If MsgBox("The date field is null. Closing this form without a
date
will delete this record." & vbNewLine & "If you wish to proceed click OK."
&
vbNewLine & "If you wish to enter a date, Click Cancel.", vbOKCancel,
"WARNING!") = vbCancel Then
Cancel = True
Me.dtmDate.SetFocus

Else: DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
stLinkCriteria = "[ClientCaseInfoId]= " & Me![ClientCaseInfoID]
DoCmd.OpenForm "frmSummary", , , stLinkCriteria, , , ClientCaseInfoID

End If
End If

End Sub

The scenario I envisioned for the command button was that there would be
times when there would be no time and no date entered in the list boxes on
frmContact and it would be used simply as a way to pass the
ClientCaseInfoID
into frmSummary. At other times there WOULD be time and a date listed on
frmContact, so that the current record would be saved and THEN the
ClientCaseInfoID would be passed into frmSummary. When a date is not
entered, the code works fine. But, if there is a date entered, the event
behind the label doesn't seem to fire. In other words, frmSummary does
not
open. I HAVE to be in a record that has no data (other than the
ClientCaseInfoID) entered in order for frmSummary to open when I click the
label.

Any help is appreciated!
Click to expand...

I'm not certain that I understand your form design completely from your
description, but it sounds like you might need to put some code into the
form's Current event that positions the listbox selections to the
appropriate values.

As for the second issue, you have the statement

If IsNull(dtmDate) Then...

at the beginning of your event. I suspect that the event is firing, but for
records that have a date entered the body of the event is being skipped. You
can test this by placing a breakpoint on the If IsNull... statement and see
if the code stops there.

I think this is more like you would want:

Private Sub Label43_Click()
Dim stLinkCriteria As String

If IsNull(dtmDate) Then
Me.dtmDate.BackColor = 255
If MsgBox("The date field is null. Closing this form without a date
will delete this record." & vbNewLine & "If you wish to proceed click OK." &
vbNewLine & "If you wish to enter a date, Click Cancel.", vbOKCancel,
"WARNING!") = vbCancel Then
Cancel = True
Me.dtmDate.SetFocus
Exit Sub
End If
End If
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
stLinkCriteria = "[ClientCaseInfoId]= " & Me![ClientCaseInfoID]
DoCmd.OpenForm "frmSummary", , , stLinkCriteria, , , ClientCaseInfoID
End Sub

Carl Rapson
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

Don't save record 4
Excel Worksheet_BeforeDelete code not working as expected. 1
If...Then...ElseIf 8
ListBox Value One form to another without filter applied 4
enabling ctrls on check not behaving as expected 4
Command button code not opening my form 1
Problem editing Data in form 2
ListCount behaving not as expected. 3

Top