Still need assistance

F

fysh1

I posted yesterday and tried the suggestion in different
variations, but it still doesn't want to work. Here is
what I posted.
It seems the further I progress the more problems come
up. I have a form with a subform. I am trying to make
sure the customer fills in the textboxes and then chooses
an item from a combobox and then select the items from a
listbox. Right now I am having a problem if an individual
does not choose an item from the list box it should tell
the customer to select an item. Instead I get an error
message "Object variable or With block variable not set".
I tried several variations and attempted to find it in
help. Either case I have not been lucky. Can someone
assist me on this?

Private Sub cmdDone_Click()
On Error GoTo Err_cmdDone_Click

Dim vClinic As Variant
Dim ctl As Control
Dim varItm As Variant
Dim db As Database
Dim rst As Recordset
Dim strWhere As String
Set ctl = Me!subSignIn!TypeList
vClinic = Me!subSignIn!ClinicList


If IsNull(Me.FName) Then
DoCmd.CancelEvent
Beep
MsgBox "Please Provide Your First Name", , "First
Name"
FName.SetFocus
ElseIf IsNull(Me.LName) Then
DoCmd.CancelEvent
Beep
MsgBox "Please Provide Your Last Name", , "Last
Name"
LName.SetFocus
ElseIf IsNull(Me!subSignIn!ClinicList) Then
DoCmd.CancelEvent
Beep
MsgBox "Select a Clinic and Appointment for
Visit", vbCritical, "SELECT A CLINIC AND APPOINTMENT TYPES"
subSignIn!ClinicList.Dropdown
Me.subSignIn.SetFocus
Me.subSignIn!ClinicList.SetFocus
ElseIf Me!subSignIn!TypeList < 1 Then
DoCmd.CancelEvent
Beep
MsgBox "Select a Clinic and Appointment for
Visit", vbCritical, "SELECT A CLINIC AND APPOINTMENT TYPES"
Me.subSignIn.SetFocus
Me.subSignIn!TypeList.SetFocus
Else
Set db = CurrentDb()
For Each varItm In ctl.ItemsSelected
Set rst = db.OpenRecordset("tblVisit")
With rst
rst.AddNew
strWhere = "ClinicID=" & vClinic & " AND TypeID=" &
ctl.ItemData(varItm)
rst![ClinicType] = DLookup
("ClinicTypeID", "tblClinicType", strWhere)
rst![CustomerID] = Me.CustomerID
rst![CustDate] = date
rst![Timein] = time()
rst.Update
End With
Next varItm
rst.Close
db.Close
Me!subSignIn!ClinicList = Null
Me!subSignIn!TypeList.Requery
DoCmd.GoToRecord , , acNewRec
End If
Exit_cmdDone_Click:
Exit Sub
Err_cmdDone_Click:
MsgBox Err.Description
Resume Exit_cmdDone_Click
End Sub

Here is something that got me a little closer but I still
the message "Object variable or With block variable not
set". If I click on the OK button and can proceed to
choose an item, but I need to get rid of that message.

Dim i As Integer
ElseIf IsNull(Me!subSignIn!TypeList.Selected(i)) Then
DoCmd.CancelEvent
Beep
MsgBox "Select a Clinic and Appointment for
Visit", vbCritical, "SELECT A CLINIC AND APPOINTMENT TYPES"
Me.subSignIn.SetFocus
Me.subSignIn!TypeList.SetFocus

Any suggestions to solve this would be appreciated.
Thanks for taking the time to read this.
 
F

fysh1

What I want to happen is if any of the information is not
complete then I have the user go back and fill or select
the appropriate information. The problem I am finding is
that I have a combobox and a listbox on a subform. Both
of these are unbound. Once a customer selects an item
from the combobox the listbox automatically fills in.
Then the customer is suppose to select at least one of the
items from the listbox before selecting the done button.
I am trying to make this as dummy proof for the customers
as musch as possible. I have every thing the way I think
it should be and is runny smoothly except if a customer
does not select an item from the listbox I get an
unexpected error. I have rewritten it several times
various ways but nothing seems to work. I either get the
error message stated before or I get another error
message. This is the closest I have gotten it to run, but
I still get the error message if an item is not select
before selecting the done button.

ElseIf Me!subSignIn!TypeList < 0 Then
DoCmd.CancelEvent
Beep
MsgBox "Select Appointment for Visit",
vbCritical, "SELECT APPOINTMENT TYPES"


I am trying to cancel the the Click of a command button.
I hope this makes sense. Thanks for any suggestions you
may have to resolve this.

-----Original Message-----
Suggestions:

What event are you trying to cancel?
The Click of a command button is not a cancelable event.

Is there a reason you are using an unbound form here?
A bound form would obviate the need to open the recordset and append the
record programatically, and would expose a cancelable Form_BeforeUpdate
event.

Your code checks only one thing at a time.
Would it be a better interface to notify the user of all the things that are
wrong rather than they fix one item and then there is another, and another
later, and ...?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

fysh1 said:
I posted yesterday and tried the suggestion in different
variations, but it still doesn't want to work. Here is
what I posted.
It seems the further I progress the more problems come
up. I have a form with a subform. I am trying to make
sure the customer fills in the textboxes and then chooses
an item from a combobox and then select the items from a
listbox. Right now I am having a problem if an individual
does not choose an item from the list box it should tell
the customer to select an item. Instead I get an error
message "Object variable or With block variable not set".
I tried several variations and attempted to find it in
help. Either case I have not been lucky. Can someone
assist me on this?

Private Sub cmdDone_Click()
On Error GoTo Err_cmdDone_Click

Dim vClinic As Variant
Dim ctl As Control
Dim varItm As Variant
Dim db As Database
Dim rst As Recordset
Dim strWhere As String
Set ctl = Me!subSignIn!TypeList
vClinic = Me!subSignIn!ClinicList


If IsNull(Me.FName) Then
DoCmd.CancelEvent
Beep
MsgBox "Please Provide Your First Name", , "First
Name"
FName.SetFocus
ElseIf IsNull(Me.LName) Then
DoCmd.CancelEvent
Beep
MsgBox "Please Provide Your Last Name", , "Last
Name"
LName.SetFocus
ElseIf IsNull(Me!subSignIn!ClinicList) Then
DoCmd.CancelEvent
Beep
MsgBox "Select a Clinic and Appointment for
Visit", vbCritical, "SELECT A CLINIC AND APPOINTMENT TYPES"
subSignIn!ClinicList.Dropdown
Me.subSignIn.SetFocus
Me.subSignIn!ClinicList.SetFocus
ElseIf Me!subSignIn!TypeList < 1 Then
DoCmd.CancelEvent
Beep
MsgBox "Select a Clinic and Appointment for
Visit", vbCritical, "SELECT A CLINIC AND APPOINTMENT TYPES"
Me.subSignIn.SetFocus
Me.subSignIn!TypeList.SetFocus
Else
Set db = CurrentDb()
For Each varItm In ctl.ItemsSelected
Set rst = db.OpenRecordset("tblVisit")
With rst
rst.AddNew
strWhere = "ClinicID=" & vClinic & " AND TypeID=" &
ctl.ItemData(varItm)
rst![ClinicType] = DLookup
("ClinicTypeID", "tblClinicType", strWhere)
rst![CustomerID] = Me.CustomerID
rst![CustDate] = date
rst![Timein] = time()
rst.Update
End With
Next varItm
rst.Close
db.Close
Me!subSignIn!ClinicList = Null
Me!subSignIn!TypeList.Requery
DoCmd.GoToRecord , , acNewRec
End If
Exit_cmdDone_Click:
Exit Sub
Err_cmdDone_Click:
MsgBox Err.Description
Resume Exit_cmdDone_Click
End Sub

Here is something that got me a little closer but I still
the message "Object variable or With block variable not
set". If I click on the OK button and can proceed to
choose an item, but I need to get rid of that message.

Dim i As Integer
ElseIf IsNull(Me!subSignIn!TypeList.Selected(i)) Then
DoCmd.CancelEvent
Beep
MsgBox "Select a Clinic and Appointment for
Visit", vbCritical, "SELECT A CLINIC AND APPOINTMENT TYPES"
Me.subSignIn.SetFocus
Me.subSignIn!TypeList.SetFocus

Any suggestions to solve this would be appreciated.
Thanks for taking the time to read this.
Click to expand...


.
Click to expand...
 
F

fysh1

I got it, I got it. It took me a while, but I got it.
Thanks to those who responded anout this. Here it is:
ElseIf Me!subSignIn!TypeList.ListIndex < 0 Then
DoCmd.CancelEvent
Beep
MsgBox "Select Appointment for Visit",
vbCritical, "SELECT APPOINTMENT TYPES"

-----Original Message-----
Suggestions:

What event are you trying to cancel?
The Click of a command button is not a cancelable event.

Is there a reason you are using an unbound form here?
A bound form would obviate the need to open the recordset and append the
record programatically, and would expose a cancelable Form_BeforeUpdate
event.

Your code checks only one thing at a time.
Would it be a better interface to notify the user of all the things that are
wrong rather than they fix one item and then there is another, and another
later, and ...?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

fysh1 said:
I posted yesterday and tried the suggestion in different
variations, but it still doesn't want to work. Here is
what I posted.
It seems the further I progress the more problems come
up. I have a form with a subform. I am trying to make
sure the customer fills in the textboxes and then chooses
an item from a combobox and then select the items from a
listbox. Right now I am having a problem if an individual
does not choose an item from the list box it should tell
the customer to select an item. Instead I get an error
message "Object variable or With block variable not set".
I tried several variations and attempted to find it in
help. Either case I have not been lucky. Can someone
assist me on this?

Private Sub cmdDone_Click()
On Error GoTo Err_cmdDone_Click

Dim vClinic As Variant
Dim ctl As Control
Dim varItm As Variant
Dim db As Database
Dim rst As Recordset
Dim strWhere As String
Set ctl = Me!subSignIn!TypeList
vClinic = Me!subSignIn!ClinicList


If IsNull(Me.FName) Then
DoCmd.CancelEvent
Beep
MsgBox "Please Provide Your First Name", , "First
Name"
FName.SetFocus
ElseIf IsNull(Me.LName) Then
DoCmd.CancelEvent
Beep
MsgBox "Please Provide Your Last Name", , "Last
Name"
LName.SetFocus
ElseIf IsNull(Me!subSignIn!ClinicList) Then
DoCmd.CancelEvent
Beep
MsgBox "Select a Clinic and Appointment for
Visit", vbCritical, "SELECT A CLINIC AND APPOINTMENT TYPES"
subSignIn!ClinicList.Dropdown
Me.subSignIn.SetFocus
Me.subSignIn!ClinicList.SetFocus
ElseIf Me!subSignIn!TypeList < 1 Then
DoCmd.CancelEvent
Beep
MsgBox "Select a Clinic and Appointment for
Visit", vbCritical, "SELECT A CLINIC AND APPOINTMENT TYPES"
Me.subSignIn.SetFocus
Me.subSignIn!TypeList.SetFocus
Else
Set db = CurrentDb()
For Each varItm In ctl.ItemsSelected
Set rst = db.OpenRecordset("tblVisit")
With rst
rst.AddNew
strWhere = "ClinicID=" & vClinic & " AND TypeID=" &
ctl.ItemData(varItm)
rst![ClinicType] = DLookup
("ClinicTypeID", "tblClinicType", strWhere)
rst![CustomerID] = Me.CustomerID
rst![CustDate] = date
rst![Timein] = time()
rst.Update
End With
Next varItm
rst.Close
db.Close
Me!subSignIn!ClinicList = Null
Me!subSignIn!TypeList.Requery
DoCmd.GoToRecord , , acNewRec
End If
Exit_cmdDone_Click:
Exit Sub
Err_cmdDone_Click:
MsgBox Err.Description
Resume Exit_cmdDone_Click
End Sub

Here is something that got me a little closer but I still
the message "Object variable or With block variable not
set". If I click on the OK button and can proceed to
choose an item, but I need to get rid of that message.

Dim i As Integer
ElseIf IsNull(Me!subSignIn!TypeList.Selected(i)) Then
DoCmd.CancelEvent
Beep
MsgBox "Select a Clinic and Appointment for
Visit", vbCritical, "SELECT A CLINIC AND APPOINTMENT TYPES"
Me.subSignIn.SetFocus
Me.subSignIn!TypeList.SetFocus

Any suggestions to solve this would be appreciated.
Thanks for taking the time to read this.
Click to expand...


.
Click to expand...
 
S

SteveS

You need to revise your code in two places....

change to 'WITH rst' to

With rst
.AddNew
strWhere = "ClinicID=" & vClinic & " AND TypeID=" &
ctl.ItemData(varItm)
![ClinicType] = DLookup "ClinicTypeID", "tblClinicType",
strWhere)
![CustomerID] = Me.CustomerID
![CustDate] = date
![Timein] = time()
.Update
End With


You are opening the recordset within a For...Nest loop
(very bad). If there are 5 items selected in the listbox,
you are re-opening the recordset every time it loops.
Move it (set rst=...) up one line outside of the For
statement....
 

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

Help needed with multiple confusion 0
VBA works in orignal but fails in copy of database 1
Listbox need help 0
Canceling an OnClick Event 3
Switchboard form no longer fills 1
Form to pass strWhere to open report 2
no duplicate record 6
Send Appointments from Access 2007 to someone else's Outlook Calen 0

Top