Generation of new subform record based on combobox AND main form

[kevin via AccessMonster.com]

I have an unbound combobox (cboDOVSearchChart) whose row source is a query
that brings up all of the dates associated with a particular medical record
number. It consists of two columns, chart ID (hidden), and date. Each
combination of MR and date yields a unique (autonumber) chart ID. The first
column serves as the link field to associate with a subform that is based off
of chart ID and contains all of the chart information.
Is there any way to use the NotInList event for this combobox to create a new
record in the subform that will contain the next autonumber for Chart ID with
the MR number from the main chart and the date of visit that is entered in
the combobox (and then display this record)? The table (Patient_Visits) off
of which the subform is based has both MR and date of visit as required
fields; could this be causing the problem?
Thank you in advance for any direction/advice you can offer.

 

[Guest]

Kevin,

The easiest way to handle this is to have a "New Visit" button right beside
the combo box. This button would have code in its Click event that inserts a
new record in your table for the current Record Number (you don't indicate
where that is stored) and the current date (if it doesn't already exist).
Then, the code would refresh the cboDOVSearchChart control, set its value to
todays date, then perform the AfterUpdate event of that control. It might
look something like:

Private sub cmd_NewVisit_Click

Dim strSQL as string

strSQL = "INSERT INTO tbl_CustomerVisits(MR, Date()) " _
& "Values (" & [MR_ID] & ", #" & Date() & "#)"
currentdb.execute strsql

me.cboDOVSearchChart.requery
me.cboDOVSearchChart = Date()

Call cboDOVSearchChart_AfterUpdate

END SUB

This was a little simplistic, but should give you an idea of how to proceed.
You will have to change the reference to [MR_ID] to reflect the value of
your medical record number.

You could also put code similiar to this in the Not_In_List event of the
combo box.

Another thought, if you are certain that you will not have multiple visits
from a person in a single day, then you can use the DATE() function,
otherwise, you might want to consider using the Now( ) function, that would
give you both date and time of the visit.

HTH
Dale

 

[kevin via AccessMonster.com]

Dale,

Thank you very much for your response. Unfortunately, I have limited
experience with VBA, and am not sure that I made all of the substitutions
correctly. "Patient_Visits" is the table containing chart id (PK) with
associated fields "Medical Record Number" and "Date of Visit". I want to
insert the value from a text box on the main form (txtChartMR), which is
based on a seperate patient information table (medical record number = PK),
and the combobox into the new record that is created. When I run the code, I
get error message 3134 "Syntax error in INSERT INTO statement." The new dates
that I will be entering will not always be the current date, so I avoided
using the DATE and Now functions. Here is my code:

Dim strSQL As String

strSQL = "INSERT INTO Patient_Visits(Medical Record Number, Date of Visit)
" _
& "Values (" & [txtChartMR] & ", #" & [cboDOVSearchChart] & "#)"
CurrentDb.Execute strSQL '=========This line is highlighted on debug

Me.cboDOVSearchChart.Requery
Me.cboDOVSearchChart = NewData

Call cboDOVSearchChart_AfterUpdate

Again, thank you very much for taking the time to help me out.

Kevin

Kevin,

The easiest way to handle this is to have a "New Visit" button right beside
the combo box. This button would have code in its Click event that inserts a
new record in your table for the current Record Number (you don't indicate
where that is stored) and the current date (if it doesn't already exist).
Then, the code would refresh the cboDOVSearchChart control, set its value to
todays date, then perform the AfterUpdate event of that control. It might
look something like:

Private sub cmd_NewVisit_Click

Dim strSQL as string

strSQL = "INSERT INTO tbl_CustomerVisits(MR, Date()) " _
& "Values (" & [MR_ID] & ", #" & Date() & "#)"
currentdb.execute strsql

me.cboDOVSearchChart.requery
me.cboDOVSearchChart = Date()

Call cboDOVSearchChart_AfterUpdate

END SUB

This was a little simplistic, but should give you an idea of how to proceed.
You will have to change the reference to [MR_ID] to reflect the value of
your medical record number.

You could also put code similiar to this in the Not_In_List event of the
combo box.

Another thought, if you are certain that you will not have multiple visits
from a person in a single day, then you can use the DATE() function,
otherwise, you might want to consider using the Now( ) function, that would
give you both date and time of the visit.

HTH
Dale

I have an unbound combobox (cboDOVSearchChart) whose row source is a query
that brings up all of the dates associated with a particular medical record

[quoted text clipped - 9 lines]

fields; could this be causing the problem?
Thank you in advance for any direction/advice you can offer.

 

[Dale Fye]

Kevin,

1. Try to avoid putting spaces in field names. If you do, every time you
use that field, you must wrap in in square brackets [Medical Record Number].
If I want spacing for readability, I put an underscore in the field name:
Medical_Record_Number.

2. When referering to a control on the current form, you should preface the
name with a reference to the form. If it is on the same control as the code
is in, you can do it like: me.txtChartMR

3. Keeping this in mind, I think your SQL statement should look like:

strSQL = "INSERT INTO Patient_Visits ([Medical Record Number], " _
& "[Date of
Visit]) " _
& "Values (" & me.[txtChartMR] & ", " _
& "#" & me.[cboDOVSearchChart] & "#)"

Since you are not using Date() or Now() where did you put this code? If you
put it in the NotInList event of the combo box, you will need to reference
the NewData parameter instead of referencing the combo box.

Dale

Dale,

Thank you very much for your response. Unfortunately, I have limited
experience with VBA, and am not sure that I made all of the substitutions
correctly. "Patient_Visits" is the table containing chart id (PK) with
associated fields "Medical Record Number" and "Date of Visit". I want to
insert the value from a text box on the main form (txtChartMR), which is
based on a seperate patient information table (medical record number =
PK),
and the combobox into the new record that is created. When I run the code,
I
get error message 3134 "Syntax error in INSERT INTO statement." The new
dates
that I will be entering will not always be the current date, so I avoided
using the DATE and Now functions. Here is my code:

Dim strSQL As String

strSQL = "INSERT INTO Patient_Visits(Medical Record Number, Date of
Visit)
" _
& "Values (" & [txtChartMR] & ", #" & [cboDOVSearchChart] &
"#)"
CurrentDb.Execute strSQL '=========This line is highlighted on debug

Me.cboDOVSearchChart.Requery
Me.cboDOVSearchChart = NewData

Call cboDOVSearchChart_AfterUpdate

Again, thank you very much for taking the time to help me out.

Kevin

Kevin,

The easiest way to handle this is to have a "New Visit" button right
beside
the combo box. This button would have code in its Click event that
inserts a
new record in your table for the current Record Number (you don't indicate
where that is stored) and the current date (if it doesn't already exist).
Then, the code would refresh the cboDOVSearchChart control, set its value
to
todays date, then perform the AfterUpdate event of that control. It might
look something like:

Private sub cmd_NewVisit_Click

Dim strSQL as string

strSQL = "INSERT INTO tbl_CustomerVisits(MR, Date()) " _
& "Values (" & [MR_ID] & ", #" & Date() & "#)"
currentdb.execute strsql

me.cboDOVSearchChart.requery
me.cboDOVSearchChart = Date()

Call cboDOVSearchChart_AfterUpdate

END SUB

This was a little simplistic, but should give you an idea of how to
proceed.
You will have to change the reference to [MR_ID] to reflect the value of
your medical record number.

You could also put code similiar to this in the Not_In_List event of the
combo box.

Another thought, if you are certain that you will not have multiple visits
from a person in a single day, then you can use the DATE() function,
otherwise, you might want to consider using the Now( ) function, that
would
give you both date and time of the visit.

HTH
Dale

I have an unbound combobox (cboDOVSearchChart) whose row source is a
query
that brings up all of the dates associated with a particular medical
record

[quoted text clipped - 9 lines]

fields; could this be causing the problem?
Thank you in advance for any direction/advice you can offer.

 

[kevin via AccessMonster.com]

Dale,

Thank you very much for your help, I think I'm almost there. The code is
entered in the NotInList event for the combobox (it is triggered by the
selection of "yes" in a message box prompt). I substituted in "NewData" and
am now getting error 3075: "Syntax error in date in query expression
'#01012001#'."
This occurs after I enter the date "01/01/2001" in the combobox. Removal of
the "#'s" leads to error 2118: "You must save the current field before you
run the Requery action." Here is the SQL I have:

strSQL = "INSERT INTO Patient_Visits ([Medical Record Number], " _
& "[Date of Visit]) " _
& "Values (" & Me.[txtChartMR] & ", " _
& "#" & NewData & "#)" '=========removal of "#'s" in this line
leads to second error

CurrentDb.Execute strSQL
Me.cboDOVSearchChart.Requery
Me.cboDOVSearchChart = NewData
Call cboDOVSearchChart_AfterUpdate

Again, thanks for the assistance. I'm going to try changing the field names
once I'm able to get this function working.

Kevin

Kevin,

1. Try to avoid putting spaces in field names. If you do, every time you
use that field, you must wrap in in square brackets [Medical Record Number].
If I want spacing for readability, I put an underscore in the field name:
Medical_Record_Number.

2. When referering to a control on the current form, you should preface the
name with a reference to the form. If it is on the same control as the code
is in, you can do it like: me.txtChartMR

3. Keeping this in mind, I think your SQL statement should look like:

strSQL = "INSERT INTO Patient_Visits ([Medical Record Number], " _
& "[Date of
Visit]) " _
& "Values (" & me.[txtChartMR] & ", " _
& "#" & me.[cboDOVSearchChart] & "#)"

Since you are not using Date() or Now() where did you put this code? If you
put it in the NotInList event of the combo box, you will need to reference
the NewData parameter instead of referencing the combo box.

Dale

Dale,

[quoted text clipped - 81 lines]

 

[Guest]

Kevin,

Try changing NewData to cdate(NewData)

--
Email address is not valid.
Please reply to newsgroup only.

Dale,

Thank you very much for your help, I think I'm almost there. The code is
entered in the NotInList event for the combobox (it is triggered by the
selection of "yes" in a message box prompt). I substituted in "NewData" and
am now getting error 3075: "Syntax error in date in query expression
'#01012001#'."
This occurs after I enter the date "01/01/2001" in the combobox. Removal of
the "#'s" leads to error 2118: "You must save the current field before you
run the Requery action." Here is the SQL I have:

strSQL = "INSERT INTO Patient_Visits ([Medical Record Number], " _
& "[Date of Visit]) " _
& "Values (" & Me.[txtChartMR] & ", " _
& "#" & NewData & "#)" '=========removal of "#'s" in this line
leads to second error

CurrentDb.Execute strSQL
Me.cboDOVSearchChart.Requery
Me.cboDOVSearchChart = NewData
Call cboDOVSearchChart_AfterUpdate

Again, thanks for the assistance. I'm going to try changing the field names
once I'm able to get this function working.

Kevin

Kevin,

1. Try to avoid putting spaces in field names. If you do, every time you
use that field, you must wrap in in square brackets [Medical Record Number].
If I want spacing for readability, I put an underscore in the field name:
Medical_Record_Number.

2. When referering to a control on the current form, you should preface the
name with a reference to the form. If it is on the same control as the code
is in, you can do it like: me.txtChartMR

3. Keeping this in mind, I think your SQL statement should look like:

strSQL = "INSERT INTO Patient_Visits ([Medical Record Number], " _
& "[Date of
Visit]) " _
& "Values (" & me.[txtChartMR] & ", " _
& "#" & me.[cboDOVSearchChart] & "#)"

Since you are not using Date() or Now() where did you put this code? If you
put it in the NotInList event of the combo box, you will need to reference
the NewData parameter instead of referencing the combo box.

Dale

Dale,

[quoted text clipped - 81 lines]

fields; could this be causing the problem?
Thank you in advance for any direction/advice you can offer.

 

[kevin via AccessMonster.com]

Dale,

Making that change yields error 13: "type mismatch" regardless of whether or
not I include the #'s in the line. I checked the table where the value is
saved; it is a date/time type. Both the combobox and the field it saves to
have the same input mask (!90\/90\/0000;;_) Here's the code:
& "#" & CDate(NewData) & "#)"
Any suggestions?

Kevin,

Try changing NewData to cdate(NewData)

Dale,

[quoted text clipped - 53 lines]

 

[Guest]

Kevin,

Try inserting the following statement immediately before the Execute
statement.

Debug.print strSQL

What gets printed in the Immediate window of the editor?

Can you post all of the code in your Not_In_List event procedure?

--
Email address is not valid.
Please reply to newsgroup only.

Dale,

Making that change yields error 13: "type mismatch" regardless of whether or
not I include the #'s in the line. I checked the table where the value is
saved; it is a date/time type. Both the combobox and the field it saves to
have the same input mask (!90\/90\/0000;;_) Here's the code:
& "#" & CDate(NewData) & "#)"
Any suggestions?

Kevin,

Try changing NewData to cdate(NewData)

Dale,

[quoted text clipped - 53 lines]

fields; could this be causing the problem?
Thank you in advance for any direction/advice you can offer.

 

[kevin via AccessMonster.com]

Dale,

Thanks so much for sticking with me. I didn't see anything change when I
entered the 'debug' line; I still get a run time error 13 "type mismatch".
Here's the notinlist code I have:

Private Sub cboDOVSearchChart_NotInList(NewData As String, Response As
Integer)

Dim ans As Variant

ans = MsgBox("The date you entered was not found. Do you want to add a new
date?", _
vbYesNo, "Add New Date?")

If ans = vbNo Then
Response = acDataErrContinue
Me.cboDOVSearchChart = Null
DoCmd.GoToControl "cboDOVSearchChart"
GoTo exit_it
End If

' add date
If ans = vbYes Then
Dim strSQL As String
strSQL = "INSERT INTO Patient_Visits ([Medical Record Number], " _
& "[Date of Visit]) " _
& "Values (" & Me.[txtChartMR] & ", " _
& "#" & CDate(NewData) & "#)" 'this line has the arrow on debug

Debug.Print strSQL 'adding this didn't seem to change anything
CurrentDb.Execute strSQL
Me.cboDOVSearchChart.Requery
Me.cboDOVSearchChart = NewData
Call cboDOVSearchChart_AfterUpdate

End If

exit_it:

End Sub

Again, thanks, and let me know if there's any other information that would
help.

Kevin

Kevin,

Try inserting the following statement immediately before the Execute
statement.

Debug.print strSQL

What gets printed in the Immediate window of the editor?

Can you post all of the code in your Not_In_List event procedure?

Dale,

[quoted text clipped - 14 lines]

 

[Dale Fye]

Kevin,

1. What is the source of the data in your cboDOVSearchChart combo box? Can
you post the SQL for that? How many fields? If more than one field, what
are the column widths and what is the bound column?

2. What format are you using to enter the new date in the combo box? Give
me an example of what you are typing in.

3. Insert the following 2 lines at the beginning of the subroutine.
Then, after you run the code, you will see a string in the debug window.
Copy that string into your reply.

debug.print NewData, varType(NewData)
debug.print cdate(NewData)

Keep your chin up, we'll get there eventually.

I'm hoping that the second line will generate an error and that the first
line will tell me what value is being passed to the routine.

Dale

Dale,

Thanks so much for sticking with me. I didn't see anything change when I
entered the 'debug' line; I still get a run time error 13 "type mismatch".
Here's the notinlist code I have:

Private Sub cboDOVSearchChart_NotInList(NewData As String, Response As
Integer)

Dim ans As Variant

ans = MsgBox("The date you entered was not found. Do you want to add a new
date?", _
vbYesNo, "Add New Date?")

If ans = vbNo Then
Response = acDataErrContinue
Me.cboDOVSearchChart = Null
DoCmd.GoToControl "cboDOVSearchChart"
GoTo exit_it
End If

' add date
If ans = vbYes Then
Dim strSQL As String
strSQL = "INSERT INTO Patient_Visits ([Medical Record Number], " _
& "[Date of Visit]) " _
& "Values (" & Me.[txtChartMR] & ", " _
& "#" & CDate(NewData) & "#)" 'this line has the arrow on debug

Debug.Print strSQL 'adding this didn't seem to change anything
CurrentDb.Execute strSQL
Me.cboDOVSearchChart.Requery
Me.cboDOVSearchChart = NewData
Call cboDOVSearchChart_AfterUpdate

End If

exit_it:

End Sub

Again, thanks, and let me know if there's any other information that would
help.

Kevin

Kevin,

Try inserting the following statement immediately before the Execute
statement.

Debug.print strSQL

What gets printed in the Immediate window of the editor?

Can you post all of the code in your Not_In_List event procedure?

Dale,

[quoted text clipped - 14 lines]

fields; could this be causing the problem?
Thank you in advance for any direction/advice you can offer.

 

[kevin via AccessMonster.com]

Responses in line:

Kevin,

1. What is the source of the data in your cboDOVSearchChart combo box? Can
you post the SQL for that? How many fields? If more than one field, what
are the column widths and what is the bound column?

Here's the row source SQL:
SELECT Patient_Visits.[Chart ID Number], Patient_Visits.[Date of Visit],
Patient_Visits.[Medical Record Number]
FROM Patient_Visits
WHERE (((Patient_Visits.[Medical Record Number])=[Forms]![editPatientInfo]!
[Medical Record Number]));

Column count is 2, widths are 0"; 1.2" and Bound Column is "1".

2. What format are you using to enter the new date in the combo box? Give
me an example of what you are typing in.

I type in "01012001" with an input mask of "!90\/90\/0000;;_" to display
"01/01/2001"

3. Insert the following 2 lines at the beginning of the subroutine.
Then, after you run the code, you will see a string in the debug window.
Copy that string into your reply.

debug.print NewData, varType(NewData)
debug.print cdate(NewData)

I entered these right after the Private Sub line, but I'm not sure how to run
it and view the debug string that you mentioned. Trying to add a date yields
error 2118 "You must save the current field before attempting a requery." I
tried going to the 'debug' menu and clicking on compile, but that didn't seem
to effect anything. Sorry, I have almost no familiarity with VBA, and thanks
again for helping me out.

Kevin

Keep your chin up, we'll get there eventually.

I'm hoping that the second line will generate an error and that the first
line will tell me what value is being passed to the routine.

Dale

Dale,

[quoted text clipped - 59 lines]

 

[Dale Fye]

Kevin,

When Access generates an error, select the DEBUG option. There should be a
box at the bottom of the editor that has a label of "Immediate". If that is
not there, go to the menu in the Editor window and select "View - Immeditate
Window".

In that box type "?newdata", without the quotes. The question mark is the
same as typing: Debug.print newdata

Then stop the code from running by clicking the "reset button on the editor
tool bar".

Now, open your form in design view, change the Bound column of our combo box
to 2. Open your form again and perform the same test as before. Again, see
what the NewData variable is actually being passed to the NotInList
subroutine.

Since you are actually trying to insert a record into a table that is being
used for a combo boxes record source, you may have to set that recordsource
to and empty string in the NotInList event, then do the insert, then reset
the rowsource to the query name or SQL string.

Dale

Responses in line:

Kevin,

1. What is the source of the data in your cboDOVSearchChart combo box?
Can
you post the SQL for that? How many fields? If more than one field, what
are the column widths and what is the bound column?

Here's the row source SQL:
SELECT Patient_Visits.[Chart ID Number], Patient_Visits.[Date of Visit],
Patient_Visits.[Medical Record Number]
FROM Patient_Visits
WHERE (((Patient_Visits.[Medical Record
Number])=[Forms]![editPatientInfo]!
[Medical Record Number]));

Column count is 2, widths are 0"; 1.2" and Bound Column is "1".

2. What format are you using to enter the new date in the combo box?
Give
me an example of what you are typing in.

I type in "01012001" with an input mask of "!90\/90\/0000;;_" to display
"01/01/2001"

3. Insert the following 2 lines at the beginning of the subroutine.
Then, after you run the code, you will see a string in the debug window.
Copy that string into your reply.

debug.print NewData, varType(NewData)
debug.print cdate(NewData)

I entered these right after the Private Sub line, but I'm not sure how to
run
it and view the debug string that you mentioned. Trying to add a date
yields
error 2118 "You must save the current field before attempting a requery."
I
tried going to the 'debug' menu and clicking on compile, but that didn't
seem
to effect anything. Sorry, I have almost no familiarity with VBA, and
thanks
again for helping me out.

Kevin

Keep your chin up, we'll get there eventually.

I'm hoping that the second line will generate an error and that the first
line will tell me what value is being passed to the routine.

Dale

Dale,

[quoted text clipped - 59 lines]

fields; could this be causing the problem?
Thank you in advance for any direction/advice you can offer.

 

[kevin via AccessMonster.com]

Dale,

Thanks for the detailed instructions. Here's what I found for the entry of
01/01/2001 (including input mask effect) with MR of 11111:
?NewData01012001 8
INSERT INTO Patient_Visits ([Medical Record Number], [Date of Visit]) Values
(11111, 01012001)

Changing the bound column from "1" to "2" led to the following for 08/08/2008:

08082008 8
INSERT INTO Patient_Visits ([Medical Record Number], [Date of Visit]) Values
(11111, 08082008)

I tried it a few times, and it seems like both come up the same way. Each
time I get the error message saying that the record must be saved before
attempting a requery. On a related note, I noticed a problem with the table
Patient_Visits that I have been trying to append to. It will not accept dates
that are entered as "01012001". It is a "Date/Time" datatype, has no Format,
and has an input mask of "!90\/90\/0000;;_". I obtained this database from
somebody who I think may have typed in every single date as "12/12/2004" or
"4/3/2006". Is there any way that I can convert all of these using either a
format/datatype/input mask so that everything is consistent with the new data
that I'm entering (the original datatype for the dates appears to have been
text)? Again, thanks a lot for your help.

Kevin

Kevin,

When Access generates an error, select the DEBUG option. There should be a
box at the bottom of the editor that has a label of "Immediate". If that is
not there, go to the menu in the Editor window and select "View - Immeditate
Window".

In that box type "?newdata", without the quotes. The question mark is the
same as typing: Debug.print newdata

Then stop the code from running by clicking the "reset button on the editor
tool bar".

Now, open your form in design view, change the Bound column of our combo box
to 2. Open your form again and perform the same test as before. Again, see
what the NewData variable is actually being passed to the NotInList
subroutine.

Since you are actually trying to insert a record into a table that is being
used for a combo boxes record source, you may have to set that recordsource
to and empty string in the NotInList event, then do the insert, then reset
the rowsource to the query name or SQL string.

Dale

Responses in line:

[quoted text clipped - 55 lines]

 

[kevin via AccessMonster.com]

Dale,

I have sorted out the problem with the input masks, and am now able to add
visit dates through an additional form (summoned by "Add Visit" command
button). However, adding the dates to the table does not refresh the combobox
listing the available dates for the patient. Also, the form chartInfo (which
contains the Add Visit button, the date combobox, and all of the other fields
for chart information) ends up behind the form for patient info which is also
open. Basically, the user selects a patient in one form, opens another to
view the chart information, and opens a third if he/she wants to add a new
visit. chartInfo is eclipsed behind the patient info chart when I close the
addVisit form. Again, thanks for your help.

Kevin

Dale,

Thanks for the detailed instructions. Here's what I found for the entry of
01/01/2001 (including input mask effect) with MR of 11111:
?NewData01012001 8
INSERT INTO Patient_Visits ([Medical Record Number], [Date of Visit]) Values
(11111, 01012001)

Changing the bound column from "1" to "2" led to the following for 08/08/2008:

08082008 8
INSERT INTO Patient_Visits ([Medical Record Number], [Date of Visit]) Values
(11111, 08082008)

I tried it a few times, and it seems like both come up the same way. Each
time I get the error message saying that the record must be saved before
attempting a requery. On a related note, I noticed a problem with the table
Patient_Visits that I have been trying to append to. It will not accept dates
that are entered as "01012001". It is a "Date/Time" datatype, has no Format,
and has an input mask of "!90\/90\/0000;;_". I obtained this database from
somebody who I think may have typed in every single date as "12/12/2004" or
"4/3/2006". Is there any way that I can convert all of these using either a
format/datatype/input mask so that everything is consistent with the new data
that I'm entering (the original datatype for the dates appears to have been
text)? Again, thanks a lot for your help.

Kevin

Kevin,

[quoted text clipped - 26 lines]