I’m very new to Access. I’ve made two continues synchronized forms “frmPatient” and “frmSample”. ”frmPatients” has a button that links to all of the records on “frmSample” that have the same Labcode. Here is the Macro I’ve used and it works.
When a new Patient is created I would like to automatically save the record because users often forget to click on the “Save” button before creating a new sample and will get an error message that a patient doesn’t exists. I’ve read I need to use
Code:
If Me.Dirty Then Me.Dirty = False
but please suggest where exactly to place this code to make this work?
Thank you,
When you enter data in a form whose control source is designated as an updateable query or table, the entered data is automatically saved when the user moves to another record, or leaves the form. If your data is not getting saved, as indicated by your comment that sometimes you have an error that a patient's record doesn't exist, then something is wrong with the way you handle data through the form.
Give us some screen shots so we can see what you're trying to do. Use sample data if anything is confidential.
I have sensitive patient information so I will post the screen shots later.
My set up is very straight forward. I have two tables Patient and Samples. Labocode is the primary key in the Patient table but the Sample table has auto generate primary key. I joined them as one-to-many joint type.
I really like the look of continues forms format rather than imbedded subform. I have a button next to each patient record “view samples” when the button is clicked it shows all of the samples associated with the patient on frmSample. It works well but as previously described with a new patient is created and the user clicks on “view samples” button to create a new sample an error message is displayed.
I’ve used the following code to copy from the previous record for the frmSamples. http://allenbrowne.com/ser-24.html Could it be causing problems?
This is the first project I’ve built in access so forgive me for my ignorance
There's probably a couple easier ways to solve this... for instance. If the user hasn't saved the record you could pop an error message when they try to start entering lab data for a patient that doesn't exist.
Also, I don't know that i'd trust users to just be allowed to enter data into the database as they go. It's just sort of leaving yourself open to problems later when, and I stress WHEN, people accidentally tarnish the integrity of the data you hold so dear to your heart.
So alllll that being said - here is how I would personally go about ensuring your users get to use your database to make their lives easier, and you get to rest easy knowing someone isn't destroying all your data on accident.
It's a multi-part process, and could probably be improved upon by someone with more coding savvy than I, but I know it works... so here goes.
I'm going to try to keep with the theme of your DB for the sake of clarity, and hopefully you can change/expand on that to get what you need. Remember that a lot of this can probably be improved upon but hopefully it's a place to start.
First up is your save button. (The code is heavily commented, it's really not as long as it seems lol)
Code:
Private Sub cmdSaveRecord_Click()
'The next 2 lines just get you set-up to add data to your existing PatientTable
Dim db As DAO.Database
Dim PatientTable As DAO.Recordset
Dim errMsg As String 'Where we will store error messages
Dim errData As Boolean 'Default = False if we have an error we will set it to True.
Dim i As Integer 'used as a counter in For..Next loops.
Dim x As Integer 'used as counter in For..Next loops.
Dim errorArray(0 To 4) As String 'Array to hold the error messages so we can 'use them if needed.
'The following If statements correspond with the controls (text boxes/combo boxes/etc) on your form.
'Edit however you see fit.
'You could get creative with these IF statements, but for the sake of
'clarity I kept them basic.
If Me.txtPatientName.Value = "" Then
errorArray(0) = "Must Enter Patient Name."
errData = True
End If
If Me.txtPatientNumber.Value = 0 Then
errorArray(1) = "Must Enter Patient Number"
errData = True
End If
If Me.cboInsuranceType.Value = "" Then
errorArray(2) = "Must Enter Insurance Type"
errData = True
End If
If Me.txtIntakeNurse.Value = "" Then
errorArray(3) = "Must Enter Intake Nurse"
errData = True
End If
If Me.txtPatientEmail.Value = "" Then
errorArray(4) = "Must Enter Patient E-mail"
errData = True
End If
'If your errData = True that means you have an error so the following code
'will run a loop to add any error messages to one string (errMsg) and then
'display them all at once to the user. It will then set the focus to your first
'control on your form and Exit Sub so the user can fix any errors and try again.
'MsgBox "errData = " & errData
If errData = True Then
i = 0
x = 0
For i = 0 To 4
If errorArray(i) <> "" Then
If x > 0 Then
errMsg = errMsg & vbNewLine & errorArray(i)
Else
errMsg = errorArray(i)
x = x + 1
End If
End If
Next i
MsgBox errMsg & vbNewLine & "Please try again."
errMsg = ""
Me.txtPatientName.SetFocus
Exit Sub
End If
'If you trust that the user inputs are good without bumping it against anything in your tables then you can delete
'or comment out this next part.
'We can pass all the data the user input on the form to this Function which will then validate the data based on
'any rules you see fit. After the Function finishes with the data it will either be True or False. If it's False, meaning
'the Data didn't pass your validation and we Exit Sub back to the Form.
If NewPatientValidate(Me.txtPatientName.Value, Me.txtPatientNumber.Value, Me.cboInsuranceType.Value, _
Me.txtIntakeNurse.Value, Me.txtPatientEmail.Value) = False Then
Me.txtPatientName.SetFocus
Exit Sub
Else
'Now that we know we have 100% of the data required... we can finally save the inputs to our table!
'Keep in mind at this part you have some freedom to add data to the table that you don't even need to have listed on form.
'I've added DateLogged as an example. You WILL need to add the DateLogged column to your table
Set db = CurrentDb()
Set PatientTable = db.OpenRecordset("tblPatients")
With PatientTable
.AddNew
!PatientName = Me.txtPatientName.Value
!PatientNumber = Me.txtPatientNumber.Value
!InsuranceType = Me.cboInsuranceType.Value
!IntakeNurse = Me.txtIntakeNurse.Value
!PatientEmail = Me.txtPatientEmail.Value
!DateLogged = Format(Date, "MM/DD/YY")
.Update
End With
End If
'Let the user know it worked.
MsgBox "This patient has been added successfully.", vbOKOnly
'Refresh Form (I usually have a refresh button on the form anyway, and i just call that again.)
cmdRefresh_Click
End Sub
Below is the Function you can use to validate your users inputs against the data in your table. You may not need to do this, but it can't hurt to have the option available.
We start by naming the function and setting the 5 inputs we want to validate against our data.
Code:
Private Function NewPatientValidate(PatientName As String, PatientNumber As Long, Insurance As String, IntakeNurse As String, _
PatientEmail As String) As Boolean
Dim db As DAO.Database
Dim PatientTable As DAO.Recordset
Set db = CurrentDb()
Set PatientTable = db.OpenRecordset("tblPatients")
Now that we have everything set up - we will run through the entire Patient Table to look for anything we don't want spoiling our pristine data!
All the checks below are completely made up since I have no idea what exactly you'd look for.
Code:
Do Until PatientTable.EOF
If PatientTable("PatientName") = PatientName And _
IntakeNurse = "Betty in Accounting" Then
MsgBox "This Patient just can't be logged by Betty from Accounting." & vbNewLine & "Please try again."
Me.txtPatientName.SetFocus
Exit Function 'Exiting a Loop once you no longer need to keep searching
'helps cut down on processing time.
Else
PatientTable.MoveNext 'if the current record in PatientTable doesn't match your parameters it moves to the next record.
End If '
Loop
If you got to this part of your code that means the data passed your checks. So we set NewPatientValidate to True.
Code:
NewPatientValidate = True
End Function
Now that the Function has passed it goes back to your cmdSaveRecord Sub and continues on with the process.
Next is a button just to refresh the form. Users can use it if they need to start over whatever.
Code:
Private Sub cmdRefresh_Click()
Me.txtPatientName.Value = ""
Me.txtPatientNumber.Value = 0
Me.cboInsuranceType.Value = ""
Me.txtIntakeNurse.Value = ""
Me.PatientEmail.Value = ""
Me.txtPatientName.Value = ""
End Sub
So last but certainly not least... I created the table and form to test this code and made sure it was functional. What you'll need:
text boxes:
txtPatientName
txtPatientNumber
txtIntakeNurse
txtPatientEmail
Combo Box:
cboInsuranceType (I had a combo box on the form i was testing on so i rolled with it...)
Command Buttons
cmdSaveRecord (where you paste the cmdSaveRecord code)
cmdRefresh (paste cmdRefresh code)
Let me know if works out for you ... like I said you could really pretty this up and make it fancy... but this IS functional to the point where you should be able to at least build up from there
Admirable effort you provide there and not incorrect. But I am sorry to say that unfortunately it is of the throw-the-baby-out-with the-bathwater sort.
Access has a built-in mechanism for saving data as GohDiamond mentioned, and constructing one yourself is mostly just a lot of PITA for little gain. Many do what you've done simply because they do not know how to handle the automatic saving and how to prevent it, if some validation fails, and simply do not ask how to. But that is no reason to do it, because when you code something you otherwise get for free, you better have some justification for it.
Most beginners can happily avail themselves of the automatic saving, and they should.
And BTW: when resetting values to start with a new record the reset is on most occasions performed to Null, not to "" or 0. Because "" or 0 is actual data, whereas a Null reverts the control to the most common state of a virgin field in a virgin record.
@MilaK
You essentially need a "Go to new record" -button in frmPatient. AS GohDiamond mentioned, just moving to a new record would save it.
THe
If Me.Dirty Then Me.Dirty = False
I would do in a macro, just before you open frmSample. This to ensure that the virgin form did save the patient 's data (parent record), whne you are about create records for samples (child records) belonging to that parent.
There's probably a couple easier ways to solve this... for instance. If the user hasn't saved the record you could pop an error message when they try to start entering lab data for a patient that doesn't exist.
Also, I don't know that i'd trust users to just be allowed to enter data into the database as they go. It's just sort of leaving yourself open to problems later when, and I stress WHEN, people accidentally tarnish the integrity of the data you hold so dear to your heart.
So alllll that being said - here is how I would personally go about ensuring your users get to use your database to make their lives easier, and you get to rest easy knowing someone isn't destroying all your data on accident.
It's a multi-part process, and could probably be improved upon by someone with more coding savvy than I, but I know it works... so here goes.
I'm going to try to keep with the theme of your DB for the sake of clarity, and hopefully you can change/expand on that to get what you need. Remember that a lot of this can probably be improved upon but hopefully it's a place to start.
First up is your save button. (The code is heavily commented, it's really not as long as it seems lol)
Code:
Private Sub cmdSaveRecord_Click()
'The next 2 lines just get you set-up to add data to your existing PatientTable
Dim db As DAO.Database
Dim PatientTable As DAO.Recordset
Dim errMsg As String 'Where we will store error messages
Dim errData As Boolean 'Default = False if we have an error we will set it to True.
Dim i As Integer 'used as a counter in For..Next loops.
Dim x As Integer 'used as counter in For..Next loops.
Dim errorArray(0 To 4) As String 'Array to hold the error messages so we can 'use them if needed.
'The following If statements correspond with the controls (text boxes/combo boxes/etc) on your form.
'Edit however you see fit.
'You could get creative with these IF statements, but for the sake of
'clarity I kept them basic.
If Me.txtPatientName.Value = "" Then
errorArray(0) = "Must Enter Patient Name."
errData = True
End If
If Me.txtPatientNumber.Value = 0 Then
errorArray(1) = "Must Enter Patient Number"
errData = True
End If
If Me.cboInsuranceType.Value = "" Then
errorArray(2) = "Must Enter Insurance Type"
errData = True
End If
If Me.txtIntakeNurse.Value = "" Then
errorArray(3) = "Must Enter Intake Nurse"
errData = True
End If
If Me.txtPatientEmail.Value = "" Then
errorArray(4) = "Must Enter Patient E-mail"
errData = True
End If
'If your errData = True that means you have an error so the following code
'will run a loop to add any error messages to one string (errMsg) and then
'display them all at once to the user. It will then set the focus to your first
'control on your form and Exit Sub so the user can fix any errors and try again.
'MsgBox "errData = " & errData
If errData = True Then
i = 0
x = 0
For i = 0 To 4
If errorArray(i) <> "" Then
If x > 0 Then
errMsg = errMsg & vbNewLine & errorArray(i)
Else
errMsg = errorArray(i)
x = x + 1
End If
End If
Next i
MsgBox errMsg & vbNewLine & "Please try again."
errMsg = ""
Me.txtPatientName.SetFocus
Exit Sub
End If
'If you trust that the user inputs are good without bumping it against anything in your tables then you can delete
'or comment out this next part.
'We can pass all the data the user input on the form to this Function which will then validate the data based on
'any rules you see fit. After the Function finishes with the data it will either be True or False. If it's False, meaning
'the Data didn't pass your validation and we Exit Sub back to the Form.
If NewPatientValidate(Me.txtPatientName.Value, Me.txtPatientNumber.Value, Me.cboInsuranceType.Value, _
Me.txtIntakeNurse.Value, Me.txtPatientEmail.Value) = False Then
Me.txtPatientName.SetFocus
Exit Sub
Else
'Now that we know we have 100% of the data required... we can finally save the inputs to our table!
'Keep in mind at this part you have some freedom to add data to the table that you don't even need to have listed on form.
'I've added DateLogged as an example. You WILL need to add the DateLogged column to your table
Set db = CurrentDb()
Set PatientTable = db.OpenRecordset("tblPatients")
With PatientTable
.AddNew
!PatientName = Me.txtPatientName.Value
!PatientNumber = Me.txtPatientNumber.Value
!InsuranceType = Me.cboInsuranceType.Value
!IntakeNurse = Me.txtIntakeNurse.Value
!PatientEmail = Me.txtPatientEmail.Value
!DateLogged = Format(Date, "MM/DD/YY")
.Update
End With
End If
'Let the user know it worked.
MsgBox "This patient has been added successfully.", vbOKOnly
'Refresh Form (I usually have a refresh button on the form anyway, and i just call that again.)
cmdRefresh_Click
End Sub
Admirable effort you provide there and not incorrect. But I am sorry to say that unfortunately it is of the throw-the-baby-out-with the-bathwater sort.
Access has a built-in mechanism for saving data as GohDiamond mentioned, and constructing one yourself is mostly just a lot of PITA for little gain. Many do what you've done simply because they do not know how to handle the automatic saving and how to prevent it, if some validation fails, and simply do not ask how to. But that is no reason to do it, because when you code something you otherwise get for free, you better have some justification for it.
Most beginners can happily avail themselves of the automatic saving, and they should.
And BTW: when resetting values to start with a new record the reset is on most occasions performed to Null, not to "" or 0. Because "" or 0 is actual data, whereas a Null reverts the control to the most common state of a virgin field in a virgin record.
@MilaK
You essentially need a "Go to new record" -button in frmPatient. AS GohDiamond mentioned, just moving to a new record would save it.
THe
If Me.Dirty Then Me.Dirty = False
I would do in a macro, just before you open frmSample. This to ensure that the virgin form did save the patient 's data (parent record), whne you are about create records for samples (child records) belonging to that parent.
You are very positive. I’m trying to wrap my brain around this Macro stuff it’s not VBA …but I’m not sure what it is. When I add If Me.Dirty Then Me.Dirty = False it doesn’t recognize Me and I’m not sure how to make this work?
Thanks,
You are welcome MilaK. spikepl is more knowledgeable than I am so following his lead might be the way to go, as he's already given me a thing or two to ponder in 2 separate replies haha.
But! Discussion is the key to learning so ...
First and foremost
Where do I put Private Sub cmdSaveRecord_Click? in the general module or button module?
It sounds like you may have figured this one out already but... if you want to add code to do something based on the manipulation of a control on your form (such as clicking a Command Botton or mousing over a textbox) you simply:
While in Design View select the control on your form that you want to add code to.In this case I selected the Save button which I had named "cmdSaveRecord"
On the properties sheet you go the the "Event" tab. You will see a bunch of different options such as "On Got Focus" or "On Mouse Move" etc.
In this case I wanted the code I posted yesterday to run when the user clicked the button so I clicked the white space next to "On Click".
You will then see on the right of those different Events 2 little boxes. If you select the "..." it will bring up a little window with a few options.
You select "Code Builder" and it will open up VBA and create the Private Sub for that control.
So in my case - it created Private Sub cmdSaveRecord_Click and I simply started coding what I wanted the button to do when it was clicked!
I have a field CP# in Patients table that is causing issues when I try to add a new record.
I get an error
Code:
!CPI# = Me.CPI.Value
"item not found in the collection" but this field is exists and spelled correctly. What should I do?
Well a few things come to mind for that error. I've had it plenty of times myself.
You'll want to ensure your controls are named properly, and then secondly go to the Patients table and make sure that field is named properly. I've seen issues with both controls and tables throw this error. I would even go so far as to ensure it's the right data type while you're in there.
But in terms of trouble shooting issues such as this, you might want to get familiar with debug.print if you aren't already. It's a simple way to watch what your code is doing AS it runs instead of looking at the results and trying to find mistakes. So in this specific instance you'll want to go into your VBA window and hit ctrl+G. This will open a window called "Immediate". (You can also open this window by going to View in the VBA window and selecting it from the drop down.)
Then simply add debug.print to your code like so:
Code:
Set db = CurrentDb()
Set PatientTable = db.OpenRecordset("tblPatients")
With PatientTable
.AddNew
!LABCODE = Me.LABCODE.Value
debug.print Me.LABCODE.Value
!CPI# = Me.CPI.Value
debug.print Me.CPI.Value
All you have to do from there is use your form as a user would and see what values are popping up in your Immediate Window.
Another option is to put message boxes in your code to have the values pop up. It works about the same, but the debug.print is more convenient in most cases. Especially if you start getting into more complex code or start doing more in SQL where the strings can get quite long.
If you want to use message boxes for this same purpose you swap out the debug.print lines with:
Code:
MsgBox "CPI Value = " & Me.CPI.Value
Past that - I would personally change the field name from CPI# to CPINum and see where that got me. For some reason the "#" in a naming convention seems suspect to me... but I do not know for certain.
