validating data and generating autonumber

[Guest]

hy
i have a form called marketers and fileds called visittype,first,last and id.

i have following code to generate the autonumber for field id based on
condition of visittype.
if visittype is first visit then only it's supposse to generate the
autonumber else the code is dlooking up a value from database based on the
last and first control of the form.
everything is working fine, but the problem is users select visttype as
first visit and then they choose last and first name from drop down box
instead of writting it down.and this code still generate the new id# for same
first and last name in databse and as a result i have a two id for same name.
is there a way i can modify this code such a way that it looks up at a
visittype as first visit and then looks up at the first and last on the
current form and then if the names are already in databse tabel gives a
massage like this name is already in the database.
thanks for your help
Private Sub doctorid_GotFocus()
Dim DOCTORID As Long




If VISITTYPE = "First Visit" Then

Forms!MARKETERS!DOCTORID = Nz(DMax("[DOCTORID]", "MARKETERS"), 0) + 1

Else: Forms!MARKETERS!DOCTORID = DLookup( _
"[DOCTORID]", "marketers", "MARKETERS.LAST = " & Chr(34 _
) & Forms!MARKETERS.last & Chr(34 _
) & " And MARKETERS.FIRST = " & Chr(34 _
) & Forms!MARKETERS.first & Chr(34))

End If
End

End Sub

 

[Guest]

any suggestions please

 

[BruceM]

I can't quite sort out what you are doing here. DMax applies to the table
on which the form is based. If Marketers is the name of both the form and
the table, you should do something to distinguish the two. What table is
the form's record source? What is being stored in that table? Is DoctorID
the combo box? If so,what is its row source? Is the combo box bound to a
field?
I can only guess that you are marketing things to doctors, and that this
database is for keeping track of marketing-related visits to various
doctors. In that case you probably need a Doctors table and a Visits table,
related one to many. But it may be that you also need to keep track of
which marketer made the visit, in which you will need another table for
marketers. Each table should contain information about a single entity
(such as visits, clients, and marketers). If one doctor can receive many
visits, you only want the doctor's name to appear in one record to which
records in other tables are related as needed. Same with marketers. If one
marketer can call on many doctors, you do not want to enter that name in
every Visits record.
The best short suggestion I can think of is that you need to restrict the
combo box to items in the list, and have some means (such as a Not In List
event for the combo box, or a command button) of opening a form to enter
doctor information.

any suggestions please
--
dave

hy
i have a form called marketers and fileds called visittype,first,last and
id.

i have following code to generate the autonumber for field id based on
condition of visittype.
if visittype is first visit then only it's supposse to generate the
autonumber else the code is dlooking up a value from database based on
the
last and first control of the form.
everything is working fine, but the problem is users select visttype as
first visit and then they choose last and first name from drop down box
instead of writting it down.and this code still generate the new id# for
same
first and last name in databse and as a result i have a two id for same
name.
is there a way i can modify this code such a way that it looks up at a
visittype as first visit and then looks up at the first and last on the
current form and then if the names are already in databse tabel gives a
massage like this name is already in the database.
thanks for your help
Private Sub doctorid_GotFocus()
Dim DOCTORID As Long




If VISITTYPE = "First Visit" Then

Forms!MARKETERS!DOCTORID = Nz(DMax("[DOCTORID]", "MARKETERS"), 0) +
1

Else: Forms!MARKETERS!DOCTORID = DLookup( _
"[DOCTORID]", "marketers", "MARKETERS.LAST = " & Chr(34 _
) & Forms!MARKETERS.last & Chr(34 _
) & " And MARKETERS.FIRST = " & Chr(34 _
) & Forms!MARKETERS.first & Chr(34))

End If
End

End Sub

 

[Guest]

Dear Mr.Bruce,
thank you for your reply.
yes your guess is right, this database is for tracking the visits to the
doctor.
and you are right too, that unfortunately i have one name for tabel and
form, and that is marketers.
the doctorid control on the form is text and not a combobox. i have written
the code in on get focus of the doctorid control. far as visits to the doctor
is concern i think if name of the doctor appears more than one time is fine.
but i dont want to have more than one doctorid for each name, thats why based
on the visittype condition, if the visittype is first visit only, the code is
generating the id# other wise it is finding the doctorid # from tabel
marketers based upon last and first name entered in the current forms(that is
marketers ofcourse) field.
the problem is my last and first name control are combo box and row source
are two different query for each control. which kind of finds a distinct last
name and then find a distinct first name based on current forms last name
from tabel.
i want to do something that if user by mistake select visittype as first and
then select last and first name from combo box it gives them a massage saying
that this name already exist in the databse, do you really want to generate
new id? if they click yes then only the doctorid is generated.my guess is if
i write something like below it should work, instead it gives me a run time
error.

Private Sub doctorid_GotFocus()

Dim DOCTORID As Long




If VISITTYPE = "First Visit" and (forms!marketers.last<>marketers.last and forms!markters!first<>marketers.first)Then

Forms!MARKETERS!DOCTORID = Nz(DMax("[DOCTORID]", "MARKETERS"), 0) + 1
elseif visittype="firstvisit" and (forms!marketers!last=marketers.last and forms!marketers!first=marketers.frist) then MsgBox("THIS NAME ALREADY EXIST")........then button option

Else: Forms!MARKETERS!DOCTORID = DLookup( _
"[DOCTORID]", "marketers", "MARKETERS.LAST = " & Chr(34 _
) & Forms!MARKETERS.last & Chr(34 _
) & " And MARKETERS.FIRST = " & Chr(34 _
) & Forms!MARKETERS.first & Chr(34))

End If
End

End Sub

i hope this might clarify the question littlebit.
i know i should have done three different tabels but now its too late to do
that.
thank you for your time and response.
thanks
--
dave

I can't quite sort out what you are doing here. DMax applies to the table
on which the form is based. If Marketers is the name of both the form and
the table, you should do something to distinguish the two. What table is
the form's record source? What is being stored in that table? Is DoctorID
the combo box? If so,what is its row source? Is the combo box bound to a
field?
I can only guess that you are marketing things to doctors, and that this
database is for keeping track of marketing-related visits to various
doctors. In that case you probably need a Doctors table and a Visits table,
related one to many. But it may be that you also need to keep track of
which marketer made the visit, in which you will need another table for
marketers. Each table should contain information about a single entity
(such as visits, clients, and marketers). If one doctor can receive many
visits, you only want the doctor's name to appear in one record to which
records in other tables are related as needed. Same with marketers. If one
marketer can call on many doctors, you do not want to enter that name in
every Visits record.
The best short suggestion I can think of is that you need to restrict the
combo box to items in the list, and have some means (such as a Not In List
event for the combo box, or a command button) of opening a form to enter
doctor information.

any suggestions please
--
dave

hy
i have a form called marketers and fileds called visittype,first,last and
id.

i have following code to generate the autonumber for field id based on
condition of visittype.
if visittype is first visit then only it's supposse to generate the
autonumber else the code is dlooking up a value from database based on
the
last and first control of the form.
everything is working fine, but the problem is users select visttype as
first visit and then they choose last and first name from drop down box
instead of writting it down.and this code still generate the new id# for
same
first and last name in databse and as a result i have a two id for same
name.
is there a way i can modify this code such a way that it looks up at a
visittype as first visit and then looks up at the first and last on the
current form and then if the names are already in databse tabel gives a
massage like this name is already in the database.
thanks for your help
Private Sub doctorid_GotFocus()
Dim DOCTORID As Long




If VISITTYPE = "First Visit" Then

Forms!MARKETERS!DOCTORID = Nz(DMax("[DOCTORID]", "MARKETERS"), 0) +
1

Else: Forms!MARKETERS!DOCTORID = DLookup( _
"[DOCTORID]", "marketers", "MARKETERS.LAST = " & Chr(34 _
) & Forms!MARKETERS.last & Chr(34 _
) & " And MARKETERS.FIRST = " & Chr(34 _
) & Forms!MARKETERS.first & Chr(34))

End If
End

End Sub

 

[BruceM]

If it is too late to change your database structure, you are in for a very
difficult time maintaining and using the database. From what I can tell,
you want to record a first visit to a doctor. Then, for another visit to
the same doctor, you will create another record, and select the doctor from
a list. If it is the first visit to the doctor, you will somehow add that
doctor's name so that it appears in the list the next time you create a
record. How do you plan to keep track of visits to a particular doctor? I
cannot think of a reliable way to assign DoctorID to a doctor without
creating a record for that doctor (not a visit record, but a doctor record).

One observation is that an underscore character for a line break in VBA
needs to be outside of the parentheses:
Chr(34) & _
or
Chr(34) _
&
Other than that, about all I can suggest is that you do something like:

If VISITTYPE = "First Visit" Then
Me.YourComboBox.Enabled = False
End If

I'm sorry to say that I do not have any other suggestions. I probably could
have helped you to design a database, but I don't think I can find a way of
forcing your design to behave as you would like.

Dear Mr.Bruce,
thank you for your reply.
yes your guess is right, this database is for tracking the visits to the
doctor.
and you are right too, that unfortunately i have one name for tabel and
form, and that is marketers.
the doctorid control on the form is text and not a combobox. i have
written
the code in on get focus of the doctorid control. far as visits to the
doctor
is concern i think if name of the doctor appears more than one time is
fine.
but i dont want to have more than one doctorid for each name, thats why
based
on the visittype condition, if the visittype is first visit only, the code
is
generating the id# other wise it is finding the doctorid # from tabel
marketers based upon last and first name entered in the current forms(that
is
marketers ofcourse) field.
the problem is my last and first name control are combo box and row source
are two different query for each control. which kind of finds a distinct
last
name and then find a distinct first name based on current forms last name
from tabel.
i want to do something that if user by mistake select visittype as first
and
then select last and first name from combo box it gives them a massage
saying
that this name already exist in the databse, do you really want to
generate
new id? if they click yes then only the doctorid is generated.my guess is
if
i write something like below it should work, instead it gives me a run
time
error.

Private Sub doctorid_GotFocus()

Dim DOCTORID As Long




If VISITTYPE = "First Visit" and
(forms!marketers.last<>marketers.last and
forms!markters!first<>marketers.first)Then

Forms!MARKETERS!DOCTORID = Nz(DMax("[DOCTORID]", "MARKETERS"), 0)
+ 1
elseif visittype="firstvisit" and (forms!marketers!last=marketers.last
and forms!marketers!first=marketers.frist) then MsgBox("THIS NAME ALREADY EXIST")........then button option

Else: Forms!MARKETERS!DOCTORID = DLookup( _
"[DOCTORID]", "marketers", "MARKETERS.LAST = " & Chr(34 _
) & Forms!MARKETERS.last & Chr(34 _
) & " And MARKETERS.FIRST = " & Chr(34 _
) & Forms!MARKETERS.first & Chr(34))

End If
End

End Sub

i hope this might clarify the question littlebit.
i know i should have done three different tabels but now its too late to
do
that.
thank you for your time and response.
thanks
--
dave

I can't quite sort out what you are doing here. DMax applies to the
table
on which the form is based. If Marketers is the name of both the form
and
the table, you should do something to distinguish the two. What table is
the form's record source? What is being stored in that table? Is
DoctorID
the combo box? If so,what is its row source? Is the combo box bound to
a
field?
I can only guess that you are marketing things to doctors, and that this
database is for keeping track of marketing-related visits to various
doctors. In that case you probably need a Doctors table and a Visits
table,
related one to many. But it may be that you also need to keep track of
which marketer made the visit, in which you will need another table for
marketers. Each table should contain information about a single entity
(such as visits, clients, and marketers). If one doctor can receive many
visits, you only want the doctor's name to appear in one record to which
records in other tables are related as needed. Same with marketers. If
one
marketer can call on many doctors, you do not want to enter that name in
every Visits record.
The best short suggestion I can think of is that you need to restrict the
combo box to items in the list, and have some means (such as a Not In
List
event for the combo box, or a command button) of opening a form to enter
doctor information.

any suggestions please
--
dave


:

hy
i have a form called marketers and fileds called visittype,first,last
and
id.

i have following code to generate the autonumber for field id based on
condition of visittype.
if visittype is first visit then only it's supposse to generate the
autonumber else the code is dlooking up a value from database based on
the
last and first control of the form.
everything is working fine, but the problem is users select visttype
as
first visit and then they choose last and first name from drop down
box
instead of writting it down.and this code still generate the new id#
for
same
first and last name in databse and as a result i have a two id for
same
name.
is there a way i can modify this code such a way that it looks up at a
visittype as first visit and then looks up at the first and last on
the
current form and then if the names are already in databse tabel gives
a
massage like this name is already in the database.
thanks for your help
Private Sub doctorid_GotFocus()
Dim DOCTORID As Long




If VISITTYPE = "First Visit" Then

Forms!MARKETERS!DOCTORID = Nz(DMax("[DOCTORID]", "MARKETERS"), 0)
+
1

Else: Forms!MARKETERS!DOCTORID = DLookup( _
"[DOCTORID]", "marketers", "MARKETERS.LAST = " & Chr(34 _
) & Forms!MARKETERS.last & Chr(34 _
) & " And MARKETERS.FIRST = " & Chr(34 _
) & Forms!MARKETERS.first & Chr(34))

End If
End

End Sub

 

[Guest]

thank your for reply.
it did gave me another option, now the question is can i change the combobox
to textbox at runtime if visittype is first visit, instead of disabling the
combo?
thanks

 

[BruceM]

You can bind a text box and a combo box to the same field, place them on top
of each other, then use VBA coding to make one or the other visible. For
instance, in the form's Current event:
If Me.VisitType = "First Visit" Then
Me.txtYourTextBox.Visible = True
Else
Me.cboYourComboBox.Visible = True
End If

You could run the same or similar code in other Events, if it suits your
needs better.

thank your for reply.
it did gave me another option, now the question is can i change the
combobox
to textbox at runtime if visittype is first visit, instead of disabling
the
combo?
thanks

--
dave

hy
i have a form called marketers and fileds called visittype,first,last and
id.

i have following code to generate the autonumber for field id based on
condition of visittype.
if visittype is first visit then only it's supposse to generate the
autonumber else the code is dlooking up a value from database based on
the
last and first control of the form.
everything is working fine, but the problem is users select visttype as
first visit and then they choose last and first name from drop down box
instead of writting it down.and this code still generate the new id# for
same
first and last name in databse and as a result i have a two id for same
name.
is there a way i can modify this code such a way that it looks up at a
visittype as first visit and then looks up at the first and last on the
current form and then if the names are already in databse tabel gives a
massage like this name is already in the database.
thanks for your help
Private Sub doctorid_GotFocus()
Dim DOCTORID As Long




If VISITTYPE = "First Visit" Then

Forms!MARKETERS!DOCTORID = Nz(DMax("[DOCTORID]", "MARKETERS"), 0) +
1

Else: Forms!MARKETERS!DOCTORID = DLookup( _
"[DOCTORID]", "marketers", "MARKETERS.LAST = " & Chr(34 _
) & Forms!MARKETERS.last & Chr(34 _
) & " And MARKETERS.FIRST = " & Chr(34 _
) & Forms!MARKETERS.first & Chr(34))

End If
End

End Sub