NotInList Combobox / Adding a new record to form PLEASE HELP

[Ryan W]

I have tried everything that I can think of, without any real results.

I have a form w/subform on it in Access 2000. My PK is MedicalRecord, with
two text boxes Lname and Fname. On the subform my PK is VisitID and FK is
MedicalRecord. These forms are linked by MedicalRecord. Also on the subform I
have VisitDate and Physician.

What I am trying to accomplish is to have a combobox lookup on the main
form that will look up the MedicalRecord and if the combobox does not find
the record, it will ask the user if they want to add the record. When the
user selects yes, a new form will appear which will allow the user to enter
the patients first name and last name and the MedicalRecord will come across
from the combobox.

I have my combobox looking up the values without any problem. I also have it
so that when it is NotInList that anothe form appears with the MedicalRecord
in the designated space, and the user has two text boxes so that they can
enter the first/last name. But when I select ok on my pop-up form, it does
not update the main form (add a new record). It is adding the new
MedicalRecord to the forms current record.

Here is what I have done:
I have three text boxes on my main form w/one combo box. Textboxes:
txtMedicalRecord, txtLname, txtFname. I used the wizard to create the look up
combo box, cboMedicalRecordSearch. When I used the wizard, it gave me code
for the look up which is in the afterupdate field:

Private Sub cboMedicalRecordSearch_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CMSLName] = '" & Me![MedicalRecord] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Here is what I have for NotInList
Private sub cboMedicalRecordSearch_NotInLIst( _
Newdata as string, Response as integer)
Dim strmsg as string
Dim rst as DAO.recordset
Dim db as DAO.database

strmsg = " ' " & Newdata & " 'is not in the list."
strmsg = strmsg & "Would you like to add it?"
If vbno = msgbox(strmsg, vbyesno +vbquestion, _
"New Patient") then
Response = acdataerrdisplay
else
set db = currentdb()
set rst = db.openrecordset ("tbldemographics")
rst.addnew
rst("MedicalRecord") = Newdata
rst.update
response = acdataerradded
rst.close
end if
end sub


Where and what would I add in order to add a new record when the user select
"yes" to add that new record? Hope that this makes sense. Any help would be
wonderful! I have been working on this for a week now.

 

[Graham Mandeno]

Hi Ryan

Just to clarify:

1. What is the datatype of your primary key, MedicalRecord?

2. What are the following properties of your combo box?
RowSource, Columns, BoundColumn, ColumnWidths

3. Is your main form bound (has a RecordSource)?

4. If so, is your combo box bound (has a ControlSource)?

5. You say that you "have it so that when it is NotInList that another form
appears with the MedicalRecord in the designated space", but I can't see
anywhere in your code that you are opening this new form. How is this
happening?

Sorry to hit you with more questions - there are just a few things that
don't quite add up yet

I'll check back later today.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have tried everything that I can think of, without any real results.

I have a form w/subform on it in Access 2000. My PK is MedicalRecord, with
two text boxes Lname and Fname. On the subform my PK is VisitID and FK is
MedicalRecord. These forms are linked by MedicalRecord. Also on the
subform I
have VisitDate and Physician.

What I am trying to accomplish is to have a combobox lookup on the main
form that will look up the MedicalRecord and if the combobox does not find
the record, it will ask the user if they want to add the record. When the
user selects yes, a new form will appear which will allow the user to
enter
the patients first name and last name and the MedicalRecord will come
across
from the combobox.

I have my combobox looking up the values without any problem. I also have
it
so that when it is NotInList that anothe form appears with the
MedicalRecord
in the designated space, and the user has two text boxes so that they can
enter the first/last name. But when I select ok on my pop-up form, it does
not update the main form (add a new record). It is adding the new
MedicalRecord to the forms current record.

Here is what I have done:
I have three text boxes on my main form w/one combo box. Textboxes:
txtMedicalRecord, txtLname, txtFname. I used the wizard to create the look
up
combo box, cboMedicalRecordSearch. When I used the wizard, it gave me code
for the look up which is in the afterupdate field:

Private Sub cboMedicalRecordSearch_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CMSLName] = '" & Me![MedicalRecord] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Here is what I have for NotInList
Private sub cboMedicalRecordSearch_NotInLIst( _
Newdata as string, Response as integer)
Dim strmsg as string
Dim rst as DAO.recordset
Dim db as DAO.database

strmsg = " ' " & Newdata & " 'is not in the list."
strmsg = strmsg & "Would you like to add it?"
If vbno = msgbox(strmsg, vbyesno +vbquestion, _
"New Patient") then
Response = acdataerrdisplay
else
set db = currentdb()
set rst = db.openrecordset ("tbldemographics")
rst.addnew
rst("MedicalRecord") = Newdata
rst.update
response = acdataerradded
rst.close
end if
end sub


Where and what would I add in order to add a new record when the user
select
"yes" to add that new record? Hope that this makes sense. Any help would
be
wonderful! I have been working on this for a week now.

 

[Ryan W]

Graham,
Thank you very much for writing back. Here are the answers to your questions:

1. What is the datatype of your primary key, MedicalRecord? This is a text
field. (Data type: MM0000000)

2. What are the following properties of your combo box? RowSource, Columns,
BoundColumn, ColumnWidths
RowSource is a query based on my tblDemographics where MedicalRecord is PK.
Columns: 4. BoundColumn: 1. ColumnWidths: ;;0";0";0";0";0";0"

3. Is your main form bound (has a RecordSource)? Yes. I created a query
based on my tblDemographics, then I made a form on that query
(qryzDemographics). On my form, I used the wizard to create a combo box that
the user can use to look up a MedicalRecord.

4. If so, is your combo box bound (has a ControlSource)? No. My combo box is
based on a table/query based on my tblDemographics. I pulled in
MedicalRecord, LName, Fname, and PatientStatus.

5. You say that you "have it so that when it is NotInList that another form
appears with the MedicalRecord in the designated space", but I can't see
anywhere in your code that you are opening this new form. How is this
happening?

I posted the wrong code. Here is the code that I am using:

The AfterUpdate/NotInList for combo box.

Private Sub cboMedicalRecord_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[MedicalRecord] = '" & Me![cboMedicalRecord] & "'"
Me.Bookmark = rs.Bookmark

End Sub



Private Sub cboMedicalRecord_NotInList( _
NewData As String, Response As Integer)
Dim mbrResponse As VbMsgBoxResult
Dim strMsg As String

strMsg = NewData & _
" isn't an existing Medical Record. " & _
"Add a new Medical Record?"
mbrResponse = msgbox(strMsg, _
vbYesNo + vbQuestion, "Invalid Medical Record")
Select Case mbrResponse
Case vbYes
DoCmd.OpenForm "frmMedicalRecordEntry", _
datamode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData



' Stop here and wait until the form
' goes away.
If IsLoaded("frmMedicalRecordEntry") Then
Response = acDataErrAdded
DoCmd.Close acForm, "frmMedicalRecordEntry"
Me.frmVisitSubform.Requery
Else
Response = acDataErrContinue
DoCmd.OpenForm "frmdemographics2", datamode:=acFormAdd
End If
Case vbNo
Response = acDataErrContinue
End Select
End Sub


When my new form appears and I enter the data, it updates the information in
the combo box, but does not add a new record. It is adding the new
information to the information that is already on the form. I hope that this
explains. Please post back and I can answer more if needed.

All I am trying to do is if the item is NotInList, the form pops up, the
user enters the new information, Lname & Fname, then when the user clicks ok
the pop up disappears and the main form has this new information on a new
record. Hope that this makes sense, and thank you for your help.

Ryan W.
California, USA

 

[Graham Mandeno]

Hi Ryan

If you'd posted that code yesterday I wouldn't have needed to ask so many
questions

I think the problem is that when you close your dialog form, you have
successfully added the new MedicalRecord to tblDemographics, but your main
form which is bound to that table (via qryzDemographics) does not know the
record has been added.

The combo box knows, because setting the NotInList response to
acDataErrAdded causes the combobox to be requeried.

What you need to do is requery the main form. At the moment you are
requerying the subform, but this should not be necessary.

After DoCmd.Close... change the line:
Me.frmVisitSubform.Requery
to:
Me.Requery

Also, use Me.RecordsetClone, not Me.Recordset.Clone (i.e. lose the dot).
RecordsetClone already exists (it is a clone of the recordset created when
the form opens). By using Me.Recordset.Clone, you are creating a new clone,
which is unnecessary overhead.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,
Thank you very much for writing back. Here are the answers to your
questions:

1. What is the datatype of your primary key, MedicalRecord? This is a text
field. (Data type: MM0000000)

2. What are the following properties of your combo box? RowSource,
Columns,
BoundColumn, ColumnWidths
RowSource is a query based on my tblDemographics where MedicalRecord is
PK.
Columns: 4. BoundColumn: 1. ColumnWidths: ;;0";0";0";0";0";0"

3. Is your main form bound (has a RecordSource)? Yes. I created a query
based on my tblDemographics, then I made a form on that query
(qryzDemographics). On my form, I used the wizard to create a combo box
that
the user can use to look up a MedicalRecord.

4. If so, is your combo box bound (has a ControlSource)? No. My combo box
is
based on a table/query based on my tblDemographics. I pulled in
MedicalRecord, LName, Fname, and PatientStatus.

5. You say that you "have it so that when it is NotInList that another
form
appears with the MedicalRecord in the designated space", but I can't see
anywhere in your code that you are opening this new form. How is this
happening?

I posted the wrong code. Here is the code that I am using:

The AfterUpdate/NotInList for combo box.

Private Sub cboMedicalRecord_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[MedicalRecord] = '" & Me![cboMedicalRecord] & "'"
Me.Bookmark = rs.Bookmark

End Sub



Private Sub cboMedicalRecord_NotInList( _
NewData As String, Response As Integer)
Dim mbrResponse As VbMsgBoxResult
Dim strMsg As String

strMsg = NewData & _
" isn't an existing Medical Record. " & _
"Add a new Medical Record?"
mbrResponse = msgbox(strMsg, _
vbYesNo + vbQuestion, "Invalid Medical Record")
Select Case mbrResponse
Case vbYes
DoCmd.OpenForm "frmMedicalRecordEntry", _
datamode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData



' Stop here and wait until the form
' goes away.
If IsLoaded("frmMedicalRecordEntry") Then
Response = acDataErrAdded
DoCmd.Close acForm, "frmMedicalRecordEntry"
Me.frmVisitSubform.Requery
Else
Response = acDataErrContinue
DoCmd.OpenForm "frmdemographics2", datamode:=acFormAdd
End If
Case vbNo
Response = acDataErrContinue
End Select
End Sub


When my new form appears and I enter the data, it updates the information
in
the combo box, but does not add a new record. It is adding the new
information to the information that is already on the form. I hope that
this
explains. Please post back and I can answer more if needed.

All I am trying to do is if the item is NotInList, the form pops up, the
user enters the new information, Lname & Fname, then when the user clicks
ok
the pop up disappears and the main form has this new information on a new
record. Hope that this makes sense, and thank you for your help.

Ryan W.
California, USA

 

[Ryan W]

Graham,
I definitely owe you a beer, thank you for your time.

I made the changes that you suggested. When I changed to me.requery, I end
up in a loop that I can not get out of. I tried moving it out the select
statement to the end, and I am still having the same difficulty. Any ideas?
--
Ryan W

Hi Ryan

If you'd posted that code yesterday I wouldn't have needed to ask so many
questions

I think the problem is that when you close your dialog form, you have
successfully added the new MedicalRecord to tblDemographics, but your main
form which is bound to that table (via qryzDemographics) does not know the
record has been added.

The combo box knows, because setting the NotInList response to
acDataErrAdded causes the combobox to be requeried.

What you need to do is requery the main form. At the moment you are
requerying the subform, but this should not be necessary.

After DoCmd.Close... change the line:
Me.frmVisitSubform.Requery
to:
Me.Requery

Also, use Me.RecordsetClone, not Me.Recordset.Clone (i.e. lose the dot).
RecordsetClone already exists (it is a clone of the recordset created when
the form opens). By using Me.Recordset.Clone, you are creating a new clone,
which is unnecessary overhead.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,
Thank you very much for writing back. Here are the answers to your
questions:

1. What is the datatype of your primary key, MedicalRecord? This is a text
field. (Data type: MM0000000)

2. What are the following properties of your combo box? RowSource,
Columns,
BoundColumn, ColumnWidths
RowSource is a query based on my tblDemographics where MedicalRecord is
PK.
Columns: 4. BoundColumn: 1. ColumnWidths: ;;0";0";0";0";0";0"

3. Is your main form bound (has a RecordSource)? Yes. I created a query
based on my tblDemographics, then I made a form on that query
(qryzDemographics). On my form, I used the wizard to create a combo box
that
the user can use to look up a MedicalRecord.

4. If so, is your combo box bound (has a ControlSource)? No. My combo box
is
based on a table/query based on my tblDemographics. I pulled in
MedicalRecord, LName, Fname, and PatientStatus.

5. You say that you "have it so that when it is NotInList that another
form
appears with the MedicalRecord in the designated space", but I can't see
anywhere in your code that you are opening this new form. How is this
happening?

I posted the wrong code. Here is the code that I am using:

The AfterUpdate/NotInList for combo box.

Private Sub cboMedicalRecord_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[MedicalRecord] = '" & Me![cboMedicalRecord] & "'"
Me.Bookmark = rs.Bookmark

End Sub



Private Sub cboMedicalRecord_NotInList( _
NewData As String, Response As Integer)
Dim mbrResponse As VbMsgBoxResult
Dim strMsg As String

strMsg = NewData & _
" isn't an existing Medical Record. " & _
"Add a new Medical Record?"
mbrResponse = msgbox(strMsg, _
vbYesNo + vbQuestion, "Invalid Medical Record")
Select Case mbrResponse
Case vbYes
DoCmd.OpenForm "frmMedicalRecordEntry", _
datamode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData



' Stop here and wait until the form
' goes away.
If IsLoaded("frmMedicalRecordEntry") Then
Response = acDataErrAdded
DoCmd.Close acForm, "frmMedicalRecordEntry"
Me.frmVisitSubform.Requery
Else
Response = acDataErrContinue
DoCmd.OpenForm "frmdemographics2", datamode:=acFormAdd
End If
Case vbNo
Response = acDataErrContinue
End Select
End Sub


When my new form appears and I enter the data, it updates the information
in
the combo box, but does not add a new record. It is adding the new
information to the information that is already on the form. I hope that
this
explains. Please post back and I can answer more if needed.

All I am trying to do is if the item is NotInList, the form pops up, the
user enters the new information, Lname & Fname, then when the user clicks
ok
the pop up disappears and the main form has this new information on a new
record. Hope that this makes sense, and thank you for your help.

Ryan W.
California, USA

 

[Ryan W]

Graham,
In addition, when I click ok on my pop-up form and I go back to my main
form, the combo box is updated and I have text boxes that are connected to
the combo box ([cbomedicalrecordsearch].column(0)) that are updated as well.
What I am noticing is though that on the form it goes to the first record in
my tblDemographics. It doesn't matter what I enter into my cbo or my pop-up,
it always goes back to the first record. Hope this helps have a better
understanding.
--
Ryan W

Hi Ryan

If you'd posted that code yesterday I wouldn't have needed to ask so many
questions

I think the problem is that when you close your dialog form, you have
successfully added the new MedicalRecord to tblDemographics, but your main
form which is bound to that table (via qryzDemographics) does not know the
record has been added.

The combo box knows, because setting the NotInList response to
acDataErrAdded causes the combobox to be requeried.

What you need to do is requery the main form. At the moment you are
requerying the subform, but this should not be necessary.

After DoCmd.Close... change the line:
Me.frmVisitSubform.Requery
to:
Me.Requery

Also, use Me.RecordsetClone, not Me.Recordset.Clone (i.e. lose the dot).
RecordsetClone already exists (it is a clone of the recordset created when
the form opens). By using Me.Recordset.Clone, you are creating a new clone,
which is unnecessary overhead.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,
Thank you very much for writing back. Here are the answers to your
questions:

1. What is the datatype of your primary key, MedicalRecord? This is a text
field. (Data type: MM0000000)

2. What are the following properties of your combo box? RowSource,
Columns,
BoundColumn, ColumnWidths
RowSource is a query based on my tblDemographics where MedicalRecord is
PK.
Columns: 4. BoundColumn: 1. ColumnWidths: ;;0";0";0";0";0";0"

3. Is your main form bound (has a RecordSource)? Yes. I created a query
based on my tblDemographics, then I made a form on that query
(qryzDemographics). On my form, I used the wizard to create a combo box
that
the user can use to look up a MedicalRecord.

4. If so, is your combo box bound (has a ControlSource)? No. My combo box
is
based on a table/query based on my tblDemographics. I pulled in
MedicalRecord, LName, Fname, and PatientStatus.

5. You say that you "have it so that when it is NotInList that another
form
appears with the MedicalRecord in the designated space", but I can't see
anywhere in your code that you are opening this new form. How is this
happening?

I posted the wrong code. Here is the code that I am using:

The AfterUpdate/NotInList for combo box.

Private Sub cboMedicalRecord_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[MedicalRecord] = '" & Me![cboMedicalRecord] & "'"
Me.Bookmark = rs.Bookmark

End Sub



Private Sub cboMedicalRecord_NotInList( _
NewData As String, Response As Integer)
Dim mbrResponse As VbMsgBoxResult
Dim strMsg As String

strMsg = NewData & _
" isn't an existing Medical Record. " & _
"Add a new Medical Record?"
mbrResponse = msgbox(strMsg, _
vbYesNo + vbQuestion, "Invalid Medical Record")
Select Case mbrResponse
Case vbYes
DoCmd.OpenForm "frmMedicalRecordEntry", _
datamode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData



' Stop here and wait until the form
' goes away.
If IsLoaded("frmMedicalRecordEntry") Then
Response = acDataErrAdded
DoCmd.Close acForm, "frmMedicalRecordEntry"
Me.frmVisitSubform.Requery
Else
Response = acDataErrContinue
DoCmd.OpenForm "frmdemographics2", datamode:=acFormAdd
End If
Case vbNo
Response = acDataErrContinue
End Select
End Sub


When my new form appears and I enter the data, it updates the information
in
the combo box, but does not add a new record. It is adding the new
information to the information that is already on the form. I hope that
this
explains. Please post back and I can answer more if needed.

All I am trying to do is if the item is NotInList, the form pops up, the
user enters the new information, Lname & Fname, then when the user clicks
ok
the pop up disappears and the main form has this new information on a new
record. Hope that this makes sense, and thank you for your help.

Ryan W.
California, USA

 

[Graham Mandeno]

Hi Ryan

Are you *sure* your combo box is unbound? Its ControlSource property should
be blank.

Reverting to the first record is understandable, because Me.Requery will do
that. However, the very next thing that happens should be
cboMedicalRecord_AfterUpdate, which should lookup, find, and change the
focus to the new record you have just added.

However, if cboMedicalRecord is bound, then the act of changing focus to a
new record (the first one) will change its value.

I don't understand wuat you mean about the loop. Is this still happening?

It's possible that some other event code is interfering with your combo
value when the form is requeried.

Ahhhh <light flicks on> You don't have cboMedicalRecord = Me.MedicalRecord
in your Form_Current event, do you? That would explain it!

Try this:

Declare a module-level variable:
Dim fRecordAdded as Boolean

Now, in cboMedicalRecord_NotInList, instead of Me.Requery, say:
fRecordAdded = True

Now, in cboMedicalRecord_AfterUpdate:
Dim sRecordToFind as String
sRecordToFind = cboMedicalRecord
If fRecordAdded Then
fRecordAdded = False
Echo False
Me.Requery
End If
With Me.RecordsetClone
.FindFirst "[MedicalRecord] = '" & sRecordToFind & "'"
Me.Bookmark = .Bookmark
End With
Echo True

Turning Echo off will stop the switch to the first record and back to the
new record being visible.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,
In addition, when I click ok on my pop-up form and I go back to my main
form, the combo box is updated and I have text boxes that are connected to
the combo box ([cbomedicalrecordsearch].column(0)) that are updated as
well.
What I am noticing is though that on the form it goes to the first record
in
my tblDemographics. It doesn't matter what I enter into my cbo or my
pop-up,
it always goes back to the first record. Hope this helps have a better
understanding.
--
Ryan W

Hi Ryan

If you'd posted that code yesterday I wouldn't have needed to ask so many
questions

I think the problem is that when you close your dialog form, you have
successfully added the new MedicalRecord to tblDemographics, but your
main
form which is bound to that table (via qryzDemographics) does not know
the
record has been added.

The combo box knows, because setting the NotInList response to
acDataErrAdded causes the combobox to be requeried.

What you need to do is requery the main form. At the moment you are
requerying the subform, but this should not be necessary.

After DoCmd.Close... change the line:
Me.frmVisitSubform.Requery
to:
Me.Requery

Also, use Me.RecordsetClone, not Me.Recordset.Clone (i.e. lose the dot).
RecordsetClone already exists (it is a clone of the recordset created
when
the form opens). By using Me.Recordset.Clone, you are creating a new
clone,
which is unnecessary overhead.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,
Thank you very much for writing back. Here are the answers to your
questions:

1. What is the datatype of your primary key, MedicalRecord? This is a
text
field. (Data type: MM0000000)

2. What are the following properties of your combo box? RowSource,
Columns,
BoundColumn, ColumnWidths
RowSource is a query based on my tblDemographics where MedicalRecord is
PK.
Columns: 4. BoundColumn: 1. ColumnWidths: ;;0";0";0";0";0";0"

3. Is your main form bound (has a RecordSource)? Yes. I created a query
based on my tblDemographics, then I made a form on that query
(qryzDemographics). On my form, I used the wizard to create a combo box
that
the user can use to look up a MedicalRecord.

4. If so, is your combo box bound (has a ControlSource)? No. My combo
box
is
based on a table/query based on my tblDemographics. I pulled in
MedicalRecord, LName, Fname, and PatientStatus.

5. You say that you "have it so that when it is NotInList that another
form
appears with the MedicalRecord in the designated space", but I can't
see
anywhere in your code that you are opening this new form. How is this
happening?

I posted the wrong code. Here is the code that I am using:

The AfterUpdate/NotInList for combo box.

Private Sub cboMedicalRecord_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[MedicalRecord] = '" & Me![cboMedicalRecord] & "'"
Me.Bookmark = rs.Bookmark

End Sub



Private Sub cboMedicalRecord_NotInList( _
NewData As String, Response As Integer)
Dim mbrResponse As VbMsgBoxResult
Dim strMsg As String

strMsg = NewData & _
" isn't an existing Medical Record. " & _
"Add a new Medical Record?"
mbrResponse = msgbox(strMsg, _
vbYesNo + vbQuestion, "Invalid Medical Record")
Select Case mbrResponse
Case vbYes
DoCmd.OpenForm "frmMedicalRecordEntry", _
datamode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData



' Stop here and wait until the form
' goes away.
If IsLoaded("frmMedicalRecordEntry") Then
Response = acDataErrAdded
DoCmd.Close acForm, "frmMedicalRecordEntry"
Me.frmVisitSubform.Requery
Else
Response = acDataErrContinue
DoCmd.OpenForm "frmdemographics2", datamode:=acFormAdd
End If
Case vbNo
Response = acDataErrContinue
End Select
End Sub


When my new form appears and I enter the data, it updates the
information
in
the combo box, but does not add a new record. It is adding the new
information to the information that is already on the form. I hope that
this
explains. Please post back and I can answer more if needed.

All I am trying to do is if the item is NotInList, the form pops up,
the
user enters the new information, Lname & Fname, then when the user
clicks
ok
the pop up disappears and the main form has this new information on a
new
record. Hope that this makes sense, and thank you for your help.

Ryan W.
California, USA

 

[Ryan W]

Graham,
I feel like a fool when I tell you it isn't working still. The form stays on
the first record, although the combo box and tbldemographics is updated. I
just don't know what else we can exhaust at this point.

I originally coded a find button that the user can insert the cursor into
the text box and then click "find" and it will find the record. I was trying
to provide a bit more functionality with coding the NotInList.

I don't want to take anymore of your time up, but if you think of anything
else or you think I should scrap this form and start a new one....I would be
up for the challenge. Thanks again for all your assistance.

Ryan

Hi Ryan

Are you *sure* your combo box is unbound? Its ControlSource property should
be blank.

Reverting to the first record is understandable, because Me.Requery will do
that. However, the very next thing that happens should be
cboMedicalRecord_AfterUpdate, which should lookup, find, and change the
focus to the new record you have just added.

However, if cboMedicalRecord is bound, then the act of changing focus to a
new record (the first one) will change its value.

I don't understand wuat you mean about the loop. Is this still happening?

It's possible that some other event code is interfering with your combo
value when the form is requeried.

Ahhhh <light flicks on> You don't have cboMedicalRecord = Me.MedicalRecord
in your Form_Current event, do you? That would explain it!

Try this:

Declare a module-level variable:
Dim fRecordAdded as Boolean

Now, in cboMedicalRecord_NotInList, instead of Me.Requery, say:
fRecordAdded = True

Now, in cboMedicalRecord_AfterUpdate:
Dim sRecordToFind as String
sRecordToFind = cboMedicalRecord
If fRecordAdded Then
fRecordAdded = False
Echo False
Me.Requery
End If
With Me.RecordsetClone
.FindFirst "[MedicalRecord] = '" & sRecordToFind & "'"
Me.Bookmark = .Bookmark
End With
Echo True

Turning Echo off will stop the switch to the first record and back to the
new record being visible.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,
In addition, when I click ok on my pop-up form and I go back to my main
form, the combo box is updated and I have text boxes that are connected to
the combo box ([cbomedicalrecordsearch].column(0)) that are updated as
well.
What I am noticing is though that on the form it goes to the first record
in
my tblDemographics. It doesn't matter what I enter into my cbo or my
pop-up,
it always goes back to the first record. Hope this helps have a better
understanding.
--
Ryan W

Hi Ryan

If you'd posted that code yesterday I wouldn't have needed to ask so many
questions

I think the problem is that when you close your dialog form, you have
successfully added the new MedicalRecord to tblDemographics, but your
main
form which is bound to that table (via qryzDemographics) does not know
the
record has been added.

The combo box knows, because setting the NotInList response to
acDataErrAdded causes the combobox to be requeried.

What you need to do is requery the main form. At the moment you are
requerying the subform, but this should not be necessary.

After DoCmd.Close... change the line:
Me.frmVisitSubform.Requery
to:
Me.Requery

Also, use Me.RecordsetClone, not Me.Recordset.Clone (i.e. lose the dot).
RecordsetClone already exists (it is a clone of the recordset created
when
the form opens). By using Me.Recordset.Clone, you are creating a new
clone,
which is unnecessary overhead.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,
Thank you very much for writing back. Here are the answers to your
questions:

1. What is the datatype of your primary key, MedicalRecord? This is a
text
field. (Data type: MM0000000)

2. What are the following properties of your combo box? RowSource,
Columns,
BoundColumn, ColumnWidths
RowSource is a query based on my tblDemographics where MedicalRecord is
PK.
Columns: 4. BoundColumn: 1. ColumnWidths: ;;0";0";0";0";0";0"

3. Is your main form bound (has a RecordSource)? Yes. I created a query
based on my tblDemographics, then I made a form on that query
(qryzDemographics). On my form, I used the wizard to create a combo box
that
the user can use to look up a MedicalRecord.

4. If so, is your combo box bound (has a ControlSource)? No. My combo
box
is
based on a table/query based on my tblDemographics. I pulled in
MedicalRecord, LName, Fname, and PatientStatus.

5. You say that you "have it so that when it is NotInList that another
form
appears with the MedicalRecord in the designated space", but I can't
see
anywhere in your code that you are opening this new form. How is this
happening?

I posted the wrong code. Here is the code that I am using:

The AfterUpdate/NotInList for combo box.

Private Sub cboMedicalRecord_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[MedicalRecord] = '" & Me![cboMedicalRecord] & "'"
Me.Bookmark = rs.Bookmark

End Sub



Private Sub cboMedicalRecord_NotInList( _
NewData As String, Response As Integer)
Dim mbrResponse As VbMsgBoxResult
Dim strMsg As String

strMsg = NewData & _
" isn't an existing Medical Record. " & _
"Add a new Medical Record?"
mbrResponse = msgbox(strMsg, _
vbYesNo + vbQuestion, "Invalid Medical Record")
Select Case mbrResponse
Case vbYes
DoCmd.OpenForm "frmMedicalRecordEntry", _
datamode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData



' Stop here and wait until the form
' goes away.
If IsLoaded("frmMedicalRecordEntry") Then
Response = acDataErrAdded
DoCmd.Close acForm, "frmMedicalRecordEntry"
Me.frmVisitSubform.Requery
Else
Response = acDataErrContinue
DoCmd.OpenForm "frmdemographics2", datamode:=acFormAdd
End If
Case vbNo
Response = acDataErrContinue
End Select
End Sub


When my new form appears and I enter the data, it updates the
information
in
the combo box, but does not add a new record. It is adding the new
information to the information that is already on the form. I hope that
this
explains. Please post back and I can answer more if needed.

All I am trying to do is if the item is NotInList, the form pops up,
the
user enters the new information, Lname & Fname, then when the user
clicks
ok
the pop up disappears and the main form has this new information on a
new
record. Hope that this makes sense, and thank you for your help.

Ryan W.
California, USA

 

[Ryan W]

What I meant by the loop is that when I click ok on my pop-up, it asks me if
I want to add the item, although I already just added it. Make sense?

Ryan
--
Ryan W

Hi Ryan

Are you *sure* your combo box is unbound? Its ControlSource property should
be blank.

Reverting to the first record is understandable, because Me.Requery will do
that. However, the very next thing that happens should be
cboMedicalRecord_AfterUpdate, which should lookup, find, and change the
focus to the new record you have just added.

However, if cboMedicalRecord is bound, then the act of changing focus to a
new record (the first one) will change its value.

I don't understand wuat you mean about the loop. Is this still happening?

It's possible that some other event code is interfering with your combo
value when the form is requeried.

Ahhhh <light flicks on> You don't have cboMedicalRecord = Me.MedicalRecord
in your Form_Current event, do you? That would explain it!

Try this:

Declare a module-level variable:
Dim fRecordAdded as Boolean

Now, in cboMedicalRecord_NotInList, instead of Me.Requery, say:
fRecordAdded = True

Now, in cboMedicalRecord_AfterUpdate:
Dim sRecordToFind as String
sRecordToFind = cboMedicalRecord
If fRecordAdded Then
fRecordAdded = False
Echo False
Me.Requery
End If
With Me.RecordsetClone
.FindFirst "[MedicalRecord] = '" & sRecordToFind & "'"
Me.Bookmark = .Bookmark
End With
Echo True

Turning Echo off will stop the switch to the first record and back to the
new record being visible.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,
In addition, when I click ok on my pop-up form and I go back to my main
form, the combo box is updated and I have text boxes that are connected to
the combo box ([cbomedicalrecordsearch].column(0)) that are updated as
well.
What I am noticing is though that on the form it goes to the first record
in
my tblDemographics. It doesn't matter what I enter into my cbo or my
pop-up,
it always goes back to the first record. Hope this helps have a better
understanding.
--
Ryan W

Hi Ryan

If you'd posted that code yesterday I wouldn't have needed to ask so many
questions

I think the problem is that when you close your dialog form, you have
successfully added the new MedicalRecord to tblDemographics, but your
main
form which is bound to that table (via qryzDemographics) does not know
the
record has been added.

The combo box knows, because setting the NotInList response to
acDataErrAdded causes the combobox to be requeried.

What you need to do is requery the main form. At the moment you are
requerying the subform, but this should not be necessary.

After DoCmd.Close... change the line:
Me.frmVisitSubform.Requery
to:
Me.Requery

Also, use Me.RecordsetClone, not Me.Recordset.Clone (i.e. lose the dot).
RecordsetClone already exists (it is a clone of the recordset created
when
the form opens). By using Me.Recordset.Clone, you are creating a new
clone,
which is unnecessary overhead.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,
Thank you very much for writing back. Here are the answers to your
questions:

1. What is the datatype of your primary key, MedicalRecord? This is a
text
field. (Data type: MM0000000)

2. What are the following properties of your combo box? RowSource,
Columns,
BoundColumn, ColumnWidths
RowSource is a query based on my tblDemographics where MedicalRecord is
PK.
Columns: 4. BoundColumn: 1. ColumnWidths: ;;0";0";0";0";0";0"

3. Is your main form bound (has a RecordSource)? Yes. I created a query
based on my tblDemographics, then I made a form on that query
(qryzDemographics). On my form, I used the wizard to create a combo box
that
the user can use to look up a MedicalRecord.

4. If so, is your combo box bound (has a ControlSource)? No. My combo
box
is
based on a table/query based on my tblDemographics. I pulled in
MedicalRecord, LName, Fname, and PatientStatus.

5. You say that you "have it so that when it is NotInList that another
form
appears with the MedicalRecord in the designated space", but I can't
see
anywhere in your code that you are opening this new form. How is this
happening?

I posted the wrong code. Here is the code that I am using:

The AfterUpdate/NotInList for combo box.

Private Sub cboMedicalRecord_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[MedicalRecord] = '" & Me![cboMedicalRecord] & "'"
Me.Bookmark = rs.Bookmark

End Sub



Private Sub cboMedicalRecord_NotInList( _
NewData As String, Response As Integer)
Dim mbrResponse As VbMsgBoxResult
Dim strMsg As String

strMsg = NewData & _
" isn't an existing Medical Record. " & _
"Add a new Medical Record?"
mbrResponse = msgbox(strMsg, _
vbYesNo + vbQuestion, "Invalid Medical Record")
Select Case mbrResponse
Case vbYes
DoCmd.OpenForm "frmMedicalRecordEntry", _
datamode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData



' Stop here and wait until the form
' goes away.
If IsLoaded("frmMedicalRecordEntry") Then
Response = acDataErrAdded
DoCmd.Close acForm, "frmMedicalRecordEntry"
Me.frmVisitSubform.Requery
Else
Response = acDataErrContinue
DoCmd.OpenForm "frmdemographics2", datamode:=acFormAdd
End If
Case vbNo
Response = acDataErrContinue
End Select
End Sub


When my new form appears and I enter the data, it updates the
information
in
the combo box, but does not add a new record. It is adding the new
information to the information that is already on the form. I hope that
this
explains. Please post back and I can answer more if needed.

All I am trying to do is if the item is NotInList, the form pops up,
the
user enters the new information, Lname & Fname, then when the user
clicks
ok
the pop up disappears and the main form has this new information on a
new
record. Hope that this makes sense, and thank you for your help.

Ryan W.
California, USA

 

[Graham Mandeno]

Hi Ryan

Use the debugger to step through your code and find out exactly what it is
doing. Set a breakpoint at the start of each of your event procedures in
question (cboMedicalRecord_NotInList, cboMedicalRecord_AfterUpdate, and
Form_Current). Be sure to comment out any Echo False lines.

The first thing to check is that the FindFirst method in
cboMedicalRecord_AfterUpdate is actually searching for the correct
MedicalRecord value. If not, then you have got to find out where the combo
box value is changing.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,
I feel like a fool when I tell you it isn't working still. The form stays
on
the first record, although the combo box and tbldemographics is updated. I
just don't know what else we can exhaust at this point.

I originally coded a find button that the user can insert the cursor into
the text box and then click "find" and it will find the record. I was
trying
to provide a bit more functionality with coding the NotInList.

I don't want to take anymore of your time up, but if you think of anything
else or you think I should scrap this form and start a new one....I would
be
up for the challenge. Thanks again for all your assistance.

Ryan

Hi Ryan

Are you *sure* your combo box is unbound? Its ControlSource property
should
be blank.

Reverting to the first record is understandable, because Me.Requery will
do
that. However, the very next thing that happens should be
cboMedicalRecord_AfterUpdate, which should lookup, find, and change the
focus to the new record you have just added.

However, if cboMedicalRecord is bound, then the act of changing focus to
a
new record (the first one) will change its value.

I don't understand wuat you mean about the loop. Is this still
happening?

It's possible that some other event code is interfering with your combo
value when the form is requeried.

Ahhhh <light flicks on> You don't have cboMedicalRecord =
Me.MedicalRecord
in your Form_Current event, do you? That would explain it!

Try this:

Declare a module-level variable:
Dim fRecordAdded as Boolean

Now, in cboMedicalRecord_NotInList, instead of Me.Requery, say:
fRecordAdded = True

Now, in cboMedicalRecord_AfterUpdate:
Dim sRecordToFind as String
sRecordToFind = cboMedicalRecord
If fRecordAdded Then
fRecordAdded = False
Echo False
Me.Requery
End If
With Me.RecordsetClone
.FindFirst "[MedicalRecord] = '" & sRecordToFind & "'"
Me.Bookmark = .Bookmark
End With
Echo True

Turning Echo off will stop the switch to the first record and back to the
new record being visible.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,
In addition, when I click ok on my pop-up form and I go back to my main
form, the combo box is updated and I have text boxes that are connected
to
the combo box ([cbomedicalrecordsearch].column(0)) that are updated as
well.
What I am noticing is though that on the form it goes to the first
record
in
my tblDemographics. It doesn't matter what I enter into my cbo or my
pop-up,
it always goes back to the first record. Hope this helps have a better
understanding.
--
Ryan W


:

Hi Ryan

If you'd posted that code yesterday I wouldn't have needed to ask so
many
questions

I think the problem is that when you close your dialog form, you have
successfully added the new MedicalRecord to tblDemographics, but your
main
form which is bound to that table (via qryzDemographics) does not know
the
record has been added.

The combo box knows, because setting the NotInList response to
acDataErrAdded causes the combobox to be requeried.

What you need to do is requery the main form. At the moment you are
requerying the subform, but this should not be necessary.

After DoCmd.Close... change the line:
Me.frmVisitSubform.Requery
to:
Me.Requery

Also, use Me.RecordsetClone, not Me.Recordset.Clone (i.e. lose the
dot).
RecordsetClone already exists (it is a clone of the recordset created
when
the form opens). By using Me.Recordset.Clone, you are creating a new
clone,
which is unnecessary overhead.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,
Thank you very much for writing back. Here are the answers to your
questions:

1. What is the datatype of your primary key, MedicalRecord? This is
a
text
field. (Data type: MM0000000)

2. What are the following properties of your combo box? RowSource,
Columns,
BoundColumn, ColumnWidths
RowSource is a query based on my tblDemographics where MedicalRecord
is
PK.
Columns: 4. BoundColumn: 1. ColumnWidths: ;;0";0";0";0";0";0"

3. Is your main form bound (has a RecordSource)? Yes. I created a
query
based on my tblDemographics, then I made a form on that query
(qryzDemographics). On my form, I used the wizard to create a combo
box
that
the user can use to look up a MedicalRecord.

4. If so, is your combo box bound (has a ControlSource)? No. My
combo
box
is
based on a table/query based on my tblDemographics. I pulled in
MedicalRecord, LName, Fname, and PatientStatus.

5. You say that you "have it so that when it is NotInList that
another
form
appears with the MedicalRecord in the designated space", but I can't
see
anywhere in your code that you are opening this new form. How is
this
happening?

I posted the wrong code. Here is the code that I am using:

The AfterUpdate/NotInList for combo box.

Private Sub cboMedicalRecord_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[MedicalRecord] = '" & Me![cboMedicalRecord] & "'"
Me.Bookmark = rs.Bookmark

End Sub



Private Sub cboMedicalRecord_NotInList( _
NewData As String, Response As Integer)
Dim mbrResponse As VbMsgBoxResult
Dim strMsg As String

strMsg = NewData & _
" isn't an existing Medical Record. " & _
"Add a new Medical Record?"
mbrResponse = msgbox(strMsg, _
vbYesNo + vbQuestion, "Invalid Medical Record")
Select Case mbrResponse
Case vbYes
DoCmd.OpenForm "frmMedicalRecordEntry", _
datamode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData



' Stop here and wait until the form
' goes away.
If IsLoaded("frmMedicalRecordEntry") Then
Response = acDataErrAdded
DoCmd.Close acForm, "frmMedicalRecordEntry"
Me.frmVisitSubform.Requery
Else
Response = acDataErrContinue
DoCmd.OpenForm "frmdemographics2",
datamode:=acFormAdd
End If
Case vbNo
Response = acDataErrContinue
End Select
End Sub


When my new form appears and I enter the data, it updates the
information
in
the combo box, but does not add a new record. It is adding the new
information to the information that is already on the form. I hope
that
this
explains. Please post back and I can answer more if needed.

All I am trying to do is if the item is NotInList, the form pops up,
the
user enters the new information, Lname & Fname, then when the user
clicks
ok
the pop up disappears and the main form has this new information on
a
new
record. Hope that this makes sense, and thank you for your help.

Ryan W.
California, USA

 

[Graham Mandeno]

Do you mean you are re-executing your NotInList procedure? If so, then it
probably indicates that some code is changing the Text property of your
combo box. Once again, stepping with the debugger should help to find where
this is happening.

Don't give up on this, Ryan! I'm sure you'll find a solution
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

What I meant by the loop is that when I click ok on my pop-up, it asks me
if
I want to add the item, although I already just added it. Make sense?

Ryan
--
Ryan W

Hi Ryan

Are you *sure* your combo box is unbound? Its ControlSource property
should
be blank.

Reverting to the first record is understandable, because Me.Requery will
do
that. However, the very next thing that happens should be
cboMedicalRecord_AfterUpdate, which should lookup, find, and change the
focus to the new record you have just added.

However, if cboMedicalRecord is bound, then the act of changing focus to
a
new record (the first one) will change its value.

I don't understand wuat you mean about the loop. Is this still
happening?

It's possible that some other event code is interfering with your combo
value when the form is requeried.

Ahhhh <light flicks on> You don't have cboMedicalRecord =
Me.MedicalRecord
in your Form_Current event, do you? That would explain it!

Try this:

Declare a module-level variable:
Dim fRecordAdded as Boolean

Now, in cboMedicalRecord_NotInList, instead of Me.Requery, say:
fRecordAdded = True

Now, in cboMedicalRecord_AfterUpdate:
Dim sRecordToFind as String
sRecordToFind = cboMedicalRecord
If fRecordAdded Then
fRecordAdded = False
Echo False
Me.Requery
End If
With Me.RecordsetClone
.FindFirst "[MedicalRecord] = '" & sRecordToFind & "'"
Me.Bookmark = .Bookmark
End With
Echo True

Turning Echo off will stop the switch to the first record and back to the
new record being visible.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,
In addition, when I click ok on my pop-up form and I go back to my main
form, the combo box is updated and I have text boxes that are connected
to
the combo box ([cbomedicalrecordsearch].column(0)) that are updated as
well.
What I am noticing is though that on the form it goes to the first
record
in
my tblDemographics. It doesn't matter what I enter into my cbo or my
pop-up,
it always goes back to the first record. Hope this helps have a better
understanding.
--
Ryan W


:

Hi Ryan

If you'd posted that code yesterday I wouldn't have needed to ask so
many
questions

I think the problem is that when you close your dialog form, you have
successfully added the new MedicalRecord to tblDemographics, but your
main
form which is bound to that table (via qryzDemographics) does not know
the
record has been added.

The combo box knows, because setting the NotInList response to
acDataErrAdded causes the combobox to be requeried.

What you need to do is requery the main form. At the moment you are
requerying the subform, but this should not be necessary.

After DoCmd.Close... change the line:
Me.frmVisitSubform.Requery
to:
Me.Requery

Also, use Me.RecordsetClone, not Me.Recordset.Clone (i.e. lose the
dot).
RecordsetClone already exists (it is a clone of the recordset created
when
the form opens). By using Me.Recordset.Clone, you are creating a new
clone,
which is unnecessary overhead.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,
Thank you very much for writing back. Here are the answers to your
questions:

1. What is the datatype of your primary key, MedicalRecord? This is
a
text
field. (Data type: MM0000000)

2. What are the following properties of your combo box? RowSource,
Columns,
BoundColumn, ColumnWidths
RowSource is a query based on my tblDemographics where MedicalRecord
is
PK.
Columns: 4. BoundColumn: 1. ColumnWidths: ;;0";0";0";0";0";0"

3. Is your main form bound (has a RecordSource)? Yes. I created a
query
based on my tblDemographics, then I made a form on that query
(qryzDemographics). On my form, I used the wizard to create a combo
box
that
the user can use to look up a MedicalRecord.

4. If so, is your combo box bound (has a ControlSource)? No. My
combo
box
is
based on a table/query based on my tblDemographics. I pulled in
MedicalRecord, LName, Fname, and PatientStatus.

5. You say that you "have it so that when it is NotInList that
another
form
appears with the MedicalRecord in the designated space", but I can't
see
anywhere in your code that you are opening this new form. How is
this
happening?

I posted the wrong code. Here is the code that I am using:

The AfterUpdate/NotInList for combo box.

Private Sub cboMedicalRecord_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[MedicalRecord] = '" & Me![cboMedicalRecord] & "'"
Me.Bookmark = rs.Bookmark

End Sub



Private Sub cboMedicalRecord_NotInList( _
NewData As String, Response As Integer)
Dim mbrResponse As VbMsgBoxResult
Dim strMsg As String

strMsg = NewData & _
" isn't an existing Medical Record. " & _
"Add a new Medical Record?"
mbrResponse = msgbox(strMsg, _
vbYesNo + vbQuestion, "Invalid Medical Record")
Select Case mbrResponse
Case vbYes
DoCmd.OpenForm "frmMedicalRecordEntry", _
datamode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData



' Stop here and wait until the form
' goes away.
If IsLoaded("frmMedicalRecordEntry") Then
Response = acDataErrAdded
DoCmd.Close acForm, "frmMedicalRecordEntry"
Me.frmVisitSubform.Requery
Else
Response = acDataErrContinue
DoCmd.OpenForm "frmdemographics2",
datamode:=acFormAdd
End If
Case vbNo
Response = acDataErrContinue
End Select
End Sub


When my new form appears and I enter the data, it updates the
information
in
the combo box, but does not add a new record. It is adding the new
information to the information that is already on the form. I hope
that
this
explains. Please post back and I can answer more if needed.

All I am trying to do is if the item is NotInList, the form pops up,
the
user enters the new information, Lname & Fname, then when the user
clicks
ok
the pop up disappears and the main form has this new information on
a
new
record. Hope that this makes sense, and thank you for your help.

Ryan W.
California, USA

 

[Ryan W]

Graham,
I have not given up yet.....haven't pulled out all my hair yet.

I don't know if this info will help. When I am in the form and fire this
code as you know the form does not update. But when I do close out of it and
go back in, that is when the form has "updated" and I am able to select and
pull the correct info. Is there a way that I can force the form to close and
reopen, maybe on gotfocus? I am trying all sorts of things now.

As requested, I stepped through the code to try to figure out where it is
not firing...still I can't figure it. I have put Me.requery in several places
and that doesn't seem to help, all that does is force the form to go into a
perpetual loop.

I am trying the code that you suggested in different ways, seeing if
something will come from it. I even made the main txtmedicalrecord into a
combo box and added the code to that to see if I can force the medical record
into that and it doesn't fire correctly.

Any other ideas....I am here still.

Have a great weekend, Ryan

Do you mean you are re-executing your NotInList procedure? If so, then it
probably indicates that some code is changing the Text property of your
combo box. Once again, stepping with the debugger should help to find where
this is happening.

Don't give up on this, Ryan! I'm sure you'll find a solution
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

What I meant by the loop is that when I click ok on my pop-up, it asks me
if
I want to add the item, although I already just added it. Make sense?

Ryan
--
Ryan W

Hi Ryan

Are you *sure* your combo box is unbound? Its ControlSource property
should
be blank.

Reverting to the first record is understandable, because Me.Requery will
do
that. However, the very next thing that happens should be
cboMedicalRecord_AfterUpdate, which should lookup, find, and change the
focus to the new record you have just added.

However, if cboMedicalRecord is bound, then the act of changing focus to
a
new record (the first one) will change its value.

I don't understand wuat you mean about the loop. Is this still
happening?

It's possible that some other event code is interfering with your combo
value when the form is requeried.

Ahhhh <light flicks on> You don't have cboMedicalRecord =
Me.MedicalRecord
in your Form_Current event, do you? That would explain it!

Try this:

Declare a module-level variable:
Dim fRecordAdded as Boolean

Now, in cboMedicalRecord_NotInList, instead of Me.Requery, say:
fRecordAdded = True

Now, in cboMedicalRecord_AfterUpdate:
Dim sRecordToFind as String
sRecordToFind = cboMedicalRecord
If fRecordAdded Then
fRecordAdded = False
Echo False
Me.Requery
End If
With Me.RecordsetClone
.FindFirst "[MedicalRecord] = '" & sRecordToFind & "'"
Me.Bookmark = .Bookmark
End With
Echo True

Turning Echo off will stop the switch to the first record and back to the
new record being visible.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand



Graham,
In addition, when I click ok on my pop-up form and I go back to my main
form, the combo box is updated and I have text boxes that are connected
to
the combo box ([cbomedicalrecordsearch].column(0)) that are updated as
well.
What I am noticing is though that on the form it goes to the first
record
in
my tblDemographics. It doesn't matter what I enter into my cbo or my
pop-up,
it always goes back to the first record. Hope this helps have a better
understanding.
--
Ryan W


:

Hi Ryan

If you'd posted that code yesterday I wouldn't have needed to ask so
many
questions

I think the problem is that when you close your dialog form, you have
successfully added the new MedicalRecord to tblDemographics, but your
main
form which is bound to that table (via qryzDemographics) does not know
the
record has been added.

The combo box knows, because setting the NotInList response to
acDataErrAdded causes the combobox to be requeried.

What you need to do is requery the main form. At the moment you are
requerying the subform, but this should not be necessary.

After DoCmd.Close... change the line:
Me.frmVisitSubform.Requery
to:
Me.Requery

Also, use Me.RecordsetClone, not Me.Recordset.Clone (i.e. lose the
dot).
RecordsetClone already exists (it is a clone of the recordset created
when
the form opens). By using Me.Recordset.Clone, you are creating a new
clone,
which is unnecessary overhead.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,
Thank you very much for writing back. Here are the answers to your
questions:

1. What is the datatype of your primary key, MedicalRecord? This is
a
text
field. (Data type: MM0000000)

2. What are the following properties of your combo box? RowSource,
Columns,
BoundColumn, ColumnWidths
RowSource is a query based on my tblDemographics where MedicalRecord
is
PK.
Columns: 4. BoundColumn: 1. ColumnWidths: ;;0";0";0";0";0";0"

3. Is your main form bound (has a RecordSource)? Yes. I created a
query
based on my tblDemographics, then I made a form on that query
(qryzDemographics). On my form, I used the wizard to create a combo
box
that
the user can use to look up a MedicalRecord.

4. If so, is your combo box bound (has a ControlSource)? No. My
combo
box
is
based on a table/query based on my tblDemographics. I pulled in
MedicalRecord, LName, Fname, and PatientStatus.

5. You say that you "have it so that when it is NotInList that
another
form
appears with the MedicalRecord in the designated space", but I can't
see
anywhere in your code that you are opening this new form. How is
this
happening?

I posted the wrong code. Here is the code that I am using:

The AfterUpdate/NotInList for combo box.

Private Sub cboMedicalRecord_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[MedicalRecord] = '" & Me![cboMedicalRecord] & "'"
Me.Bookmark = rs.Bookmark

End Sub



Private Sub cboMedicalRecord_NotInList( _
NewData As String, Response As Integer)
Dim mbrResponse As VbMsgBoxResult
Dim strMsg As String

strMsg = NewData & _
" isn't an existing Medical Record. " & _
"Add a new Medical Record?"
mbrResponse = msgbox(strMsg, _
vbYesNo + vbQuestion, "Invalid Medical Record")
Select Case mbrResponse
Case vbYes
DoCmd.OpenForm "frmMedicalRecordEntry", _
datamode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData



' Stop here and wait until the form
' goes away.
If IsLoaded("frmMedicalRecordEntry") Then
Response = acDataErrAdded
DoCmd.Close acForm, "frmMedicalRecordEntry"
Me.frmVisitSubform.Requery
Else
Response = acDataErrContinue
DoCmd.OpenForm "frmdemographics2",
datamode:=acFormAdd
End If
Case vbNo
Response = acDataErrContinue
End Select
End Sub


When my new form appears and I enter the data, it updates the
information
in
the combo box, but does not add a new record. It is adding the new
information to the information that is already on the form. I hope
that
this
explains. Please post back and I can answer more if needed.

All I am trying to do is if the item is NotInList, the form pops up,
the
user enters the new information, Lname & Fname, then when the user
clicks
ok
the pop up disappears and the main form has this new information on
a
new
record. Hope that this makes sense, and thank you for your help.

Ryan W.
California, USA

 

[Ryan W]

Graham,
Wanted you to know that I took another approach with this subject. I ended
up putting a text box on the form, did a search based on the text box, and if
it wasn't in the database I then forced the new item into the medical record
and have the user enter the rest of the demographics. (Staying on the main
form). Once the user has entered all the demographics, they click on "Add
Patient" and I coded it to update the recordset and form. I have it working
perfectly, without any problems. Should of started with this.....wasted a
week =(

Anyway, it was awesome that you helped me and thank you for your time.

Take care,
Ryan
--
Ryan W

Graham,
I have not given up yet.....haven't pulled out all my hair yet.

I don't know if this info will help. When I am in the form and fire this
code as you know the form does not update. But when I do close out of it and
go back in, that is when the form has "updated" and I am able to select and
pull the correct info. Is there a way that I can force the form to close and
reopen, maybe on gotfocus? I am trying all sorts of things now.

As requested, I stepped through the code to try to figure out where it is
not firing...still I can't figure it. I have put Me.requery in several places
and that doesn't seem to help, all that does is force the form to go into a
perpetual loop.

I am trying the code that you suggested in different ways, seeing if
something will come from it. I even made the main txtmedicalrecord into a
combo box and added the code to that to see if I can force the medical record
into that and it doesn't fire correctly.

Any other ideas....I am here still.

Have a great weekend, Ryan

Do you mean you are re-executing your NotInList procedure? If so, then it
probably indicates that some code is changing the Text property of your
combo box. Once again, stepping with the debugger should help to find where
this is happening.

Don't give up on this, Ryan! I'm sure you'll find a solution
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

What I meant by the loop is that when I click ok on my pop-up, it asks me
if
I want to add the item, although I already just added it. Make sense?

Ryan
--
Ryan W


:

Hi Ryan

Are you *sure* your combo box is unbound? Its ControlSource property
should
be blank.

Reverting to the first record is understandable, because Me.Requery will
do
that. However, the very next thing that happens should be
cboMedicalRecord_AfterUpdate, which should lookup, find, and change the
focus to the new record you have just added.

However, if cboMedicalRecord is bound, then the act of changing focus to
a
new record (the first one) will change its value.

I don't understand wuat you mean about the loop. Is this still
happening?

It's possible that some other event code is interfering with your combo
value when the form is requeried.

Ahhhh <light flicks on> You don't have cboMedicalRecord =
Me.MedicalRecord
in your Form_Current event, do you? That would explain it!

Try this:

Declare a module-level variable:
Dim fRecordAdded as Boolean

Now, in cboMedicalRecord_NotInList, instead of Me.Requery, say:
fRecordAdded = True

Now, in cboMedicalRecord_AfterUpdate:
Dim sRecordToFind as String
sRecordToFind = cboMedicalRecord
If fRecordAdded Then
fRecordAdded = False
Echo False
Me.Requery
End If
With Me.RecordsetClone
.FindFirst "[MedicalRecord] = '" & sRecordToFind & "'"
Me.Bookmark = .Bookmark
End With
Echo True

Turning Echo off will stop the switch to the first record and back to the
new record being visible.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand



Graham,
In addition, when I click ok on my pop-up form and I go back to my main
form, the combo box is updated and I have text boxes that are connected
to
the combo box ([cbomedicalrecordsearch].column(0)) that are updated as
well.
What I am noticing is though that on the form it goes to the first
record
in
my tblDemographics. It doesn't matter what I enter into my cbo or my
pop-up,
it always goes back to the first record. Hope this helps have a better
understanding.
--
Ryan W


:

Hi Ryan

If you'd posted that code yesterday I wouldn't have needed to ask so
many
questions

I think the problem is that when you close your dialog form, you have
successfully added the new MedicalRecord to tblDemographics, but your
main
form which is bound to that table (via qryzDemographics) does not know
the
record has been added.

The combo box knows, because setting the NotInList response to
acDataErrAdded causes the combobox to be requeried.

What you need to do is requery the main form. At the moment you are
requerying the subform, but this should not be necessary.

After DoCmd.Close... change the line:
Me.frmVisitSubform.Requery
to:
Me.Requery

Also, use Me.RecordsetClone, not Me.Recordset.Clone (i.e. lose the
dot).
RecordsetClone already exists (it is a clone of the recordset created
when
the form opens). By using Me.Recordset.Clone, you are creating a new
clone,
which is unnecessary overhead.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,
Thank you very much for writing back. Here are the answers to your
questions:

1. What is the datatype of your primary key, MedicalRecord? This is
a
text
field. (Data type: MM0000000)

2. What are the following properties of your combo box? RowSource,
Columns,
BoundColumn, ColumnWidths
RowSource is a query based on my tblDemographics where MedicalRecord
is
PK.
Columns: 4. BoundColumn: 1. ColumnWidths: ;;0";0";0";0";0";0"

3. Is your main form bound (has a RecordSource)? Yes. I created a
query
based on my tblDemographics, then I made a form on that query
(qryzDemographics). On my form, I used the wizard to create a combo
box
that
the user can use to look up a MedicalRecord.

4. If so, is your combo box bound (has a ControlSource)? No. My
combo
box
is
based on a table/query based on my tblDemographics. I pulled in
MedicalRecord, LName, Fname, and PatientStatus.

5. You say that you "have it so that when it is NotInList that
another
form
appears with the MedicalRecord in the designated space", but I can't
see
anywhere in your code that you are opening this new form. How is
this
happening?

I posted the wrong code. Here is the code that I am using:

The AfterUpdate/NotInList for combo box.

Private Sub cboMedicalRecord_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[MedicalRecord] = '" & Me![cboMedicalRecord] & "'"
Me.Bookmark = rs.Bookmark

End Sub



Private Sub cboMedicalRecord_NotInList( _
NewData As String, Response As Integer)
Dim mbrResponse As VbMsgBoxResult
Dim strMsg As String

strMsg = NewData & _
" isn't an existing Medical Record. " & _
"Add a new Medical Record?"
mbrResponse = msgbox(strMsg, _
vbYesNo + vbQuestion, "Invalid Medical Record")
Select Case mbrResponse
Case vbYes
DoCmd.OpenForm "frmMedicalRecordEntry", _
datamode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData



' Stop here and wait until the form
' goes away.
If IsLoaded("frmMedicalRecordEntry") Then
Response = acDataErrAdded
DoCmd.Close acForm, "frmMedicalRecordEntry"
Me.frmVisitSubform.Requery
Else
Response = acDataErrContinue
DoCmd.OpenForm "frmdemographics2",
datamode:=acFormAdd
End If
Case vbNo
Response = acDataErrContinue
End Select
End Sub


When my new form appears and I enter the data, it updates the
information
in
the combo box, but does not add a new record. It is adding the new
information to the information that is already on the form. I hope
that
this
explains. Please post back and I can answer more if needed.

All I am trying to do is if the item is NotInList, the form pops up,
the
user enters the new information, Lname & Fname, then when the user
clicks
ok
the pop up disappears and the main form has this new information on
a
new
record. Hope that this makes sense, and thank you for your help.

Ryan W.
California, USA

 

[Graham Mandeno]

Hi Ryan

Well, I'm still puzzled as to why a reasonably straightforward and well
tried and tested technique did not work for you, but I'm glad you got to a
solution in the end.

Well done!
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,
Wanted you to know that I took another approach with this subject. I ended
up putting a text box on the form, did a search based on the text box, and
if
it wasn't in the database I then forced the new item into the medical
record
and have the user enter the rest of the demographics. (Staying on the main
form). Once the user has entered all the demographics, they click on "Add
Patient" and I coded it to update the recordset and form. I have it
working
perfectly, without any problems. Should of started with this.....wasted a
week =(

Anyway, it was awesome that you helped me and thank you for your time.

Take care,
Ryan
--
Ryan W

Graham,
I have not given up yet.....haven't pulled out all my hair yet.

I don't know if this info will help. When I am in the form and fire this
code as you know the form does not update. But when I do close out of it
and
go back in, that is when the form has "updated" and I am able to select
and
pull the correct info. Is there a way that I can force the form to close
and
reopen, maybe on gotfocus? I am trying all sorts of things now.

As requested, I stepped through the code to try to figure out where it is
not firing...still I can't figure it. I have put Me.requery in several
places
and that doesn't seem to help, all that does is force the form to go into
a
perpetual loop.

I am trying the code that you suggested in different ways, seeing if
something will come from it. I even made the main txtmedicalrecord into a
combo box and added the code to that to see if I can force the medical
record
into that and it doesn't fire correctly.

Any other ideas....I am here still.

Have a great weekend, Ryan

Do you mean you are re-executing your NotInList procedure? If so, then
it
probably indicates that some code is changing the Text property of your
combo box. Once again, stepping with the debugger should help to find
where
this is happening.

Don't give up on this, Ryan! I'm sure you'll find a solution
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


What I meant by the loop is that when I click ok on my pop-up, it
asks me
if
I want to add the item, although I already just added it. Make sense?

Ryan
--
Ryan W


:

Hi Ryan

Are you *sure* your combo box is unbound? Its ControlSource
property
should
be blank.

Reverting to the first record is understandable, because Me.Requery
will
do
that. However, the very next thing that happens should be
cboMedicalRecord_AfterUpdate, which should lookup, find, and change
the
focus to the new record you have just added.

However, if cboMedicalRecord is bound, then the act of changing
focus to
a
new record (the first one) will change its value.

I don't understand wuat you mean about the loop. Is this still
happening?

It's possible that some other event code is interfering with your
combo
value when the form is requeried.

Ahhhh <light flicks on> You don't have cboMedicalRecord =
Me.MedicalRecord
in your Form_Current event, do you? That would explain it!

Try this:

Declare a module-level variable:
Dim fRecordAdded as Boolean

Now, in cboMedicalRecord_NotInList, instead of Me.Requery, say:
fRecordAdded = True

Now, in cboMedicalRecord_AfterUpdate:
Dim sRecordToFind as String
sRecordToFind = cboMedicalRecord
If fRecordAdded Then
fRecordAdded = False
Echo False
Me.Requery
End If
With Me.RecordsetClone
.FindFirst "[MedicalRecord] = '" & sRecordToFind & "'"
Me.Bookmark = .Bookmark
End With
Echo True

Turning Echo off will stop the switch to the first record and back
to the
new record being visible.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand



Graham,
In addition, when I click ok on my pop-up form and I go back to my
main
form, the combo box is updated and I have text boxes that are
connected
to
the combo box ([cbomedicalrecordsearch].column(0)) that are
updated as
well.
What I am noticing is though that on the form it goes to the first
record
in
my tblDemographics. It doesn't matter what I enter into my cbo or
my
pop-up,
it always goes back to the first record. Hope this helps have a
better
understanding.
--
Ryan W


:

Hi Ryan

If you'd posted that code yesterday I wouldn't have needed to ask
so
many
questions

I think the problem is that when you close your dialog form, you
have
successfully added the new MedicalRecord to tblDemographics, but
your
main
form which is bound to that table (via qryzDemographics) does not
know
the
record has been added.

The combo box knows, because setting the NotInList response to
acDataErrAdded causes the combobox to be requeried.

What you need to do is requery the main form. At the moment you
are
requerying the subform, but this should not be necessary.

After DoCmd.Close... change the line:
Me.frmVisitSubform.Requery
to:
Me.Requery

Also, use Me.RecordsetClone, not Me.Recordset.Clone (i.e. lose
the
dot).
RecordsetClone already exists (it is a clone of the recordset
created
when
the form opens). By using Me.Recordset.Clone, you are creating a
new
clone,
which is unnecessary overhead.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,
Thank you very much for writing back. Here are the answers to
your
questions:

1. What is the datatype of your primary key, MedicalRecord?
This is
a
text
field. (Data type: MM0000000)

2. What are the following properties of your combo box?
RowSource,
Columns,
BoundColumn, ColumnWidths
RowSource is a query based on my tblDemographics where
MedicalRecord
is
PK.
Columns: 4. BoundColumn: 1. ColumnWidths: ;;0";0";0";0";0";0"

3. Is your main form bound (has a RecordSource)? Yes. I created
a
query
based on my tblDemographics, then I made a form on that query
(qryzDemographics). On my form, I used the wizard to create a
combo
box
that
the user can use to look up a MedicalRecord.

4. If so, is your combo box bound (has a ControlSource)? No. My
combo
box
is
based on a table/query based on my tblDemographics. I pulled in
MedicalRecord, LName, Fname, and PatientStatus.

5. You say that you "have it so that when it is NotInList that
another
form
appears with the MedicalRecord in the designated space", but I
can't
see
anywhere in your code that you are opening this new form. How
is
this
happening?

I posted the wrong code. Here is the code that I am using:

The AfterUpdate/NotInList for combo box.

Private Sub cboMedicalRecord_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[MedicalRecord] = '" & Me![cboMedicalRecord] &
"'"
Me.Bookmark = rs.Bookmark

End Sub



Private Sub cboMedicalRecord_NotInList( _
NewData As String, Response As Integer)
Dim mbrResponse As VbMsgBoxResult
Dim strMsg As String

strMsg = NewData & _
" isn't an existing Medical Record. " & _
"Add a new Medical Record?"
mbrResponse = msgbox(strMsg, _
vbYesNo + vbQuestion, "Invalid Medical Record")
Select Case mbrResponse
Case vbYes
DoCmd.OpenForm "frmMedicalRecordEntry", _
datamode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData



' Stop here and wait until the form
' goes away.
If IsLoaded("frmMedicalRecordEntry") Then
Response = acDataErrAdded
DoCmd.Close acForm, "frmMedicalRecordEntry"
Me.frmVisitSubform.Requery
Else
Response = acDataErrContinue
DoCmd.OpenForm "frmdemographics2",
datamode:=acFormAdd
End If
Case vbNo
Response = acDataErrContinue
End Select
End Sub


When my new form appears and I enter the data, it updates the
information
in
the combo box, but does not add a new record. It is adding the
new
information to the information that is already on the form. I
hope
that
this
explains. Please post back and I can answer more if needed.

All I am trying to do is if the item is NotInList, the form
pops up,
the
user enters the new information, Lname & Fname, then when the
user
clicks
ok
the pop up disappears and the main form has this new
information on
a
new
record. Hope that this makes sense, and thank you for your
help.

Ryan W.
California, USA