This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
My boss asked me to do a new Access database from scratch. I’ve never done an Access database on my own before, and would appreciate any advice you guys have. This is to help employees schedule appointments for 46 medical doctors in 14 different medical offices.
As for my relationships, I hope I’ve set up everything correctly:
As you can see, I’ve set up two junction tables, but am not quite sure how to set up a search query with junction tables. I’ve looked in my two Access 2013 books, and I’ve also looked online but am not having much luck. For example, with the table Junction_Provider_Insurance, I’m not sure how to search the Junction table to pull up the doctor and if the doctor is contracted, not contracted, needs authorization or pending for the insurance. So if a doctor’s name and an insurance name such as Aetna is searched, I want the result to show that the doctor is or is not contracted with Aetna in ReportResults.
As for the search, I originally set up a wildcard search for all the search boxes. My original intention was that the user could type in anything they wanted in one or two or three of the boxes (such as doctor’s name, visit type and insurance) and do a search where the results come up. However this is not working at all and time is of the essence as I’ve already missed my deadline to turn in this a week ago.
Due to missing the deadline, I decided to change the wildcard text search to a combo box for the Doctor’s Name, Medical Office Name, Visit Type, Specialty and Insurance in the hopes the search will finally work. I’m thinking the best way to handle this is to remove the medical office, and then just have the doctor’s name, then when that comes up, to cascade into a new combo box for the visit type (new patient, follow up, pre-op, post-op, etc.) Or is there a better way to do this? I still need the results to show in the report too.
Thank you so much for your time and help. I really appreciate it very much.
A family of Microsoft relational database management systems designed for ease of use.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
There is more than one issue here. So, I am going to start with the first one. When you hit the Clear button it sounds like the Provider's Name is not requerying. I would add a Requery in the code of the Clear button. If that doesn't work then you need to make sure there is an Appointment Type for the Provider selected.
Thank you Gina. Here is a screen shot of what is happening. The search works great, then you hit the clear button and do another search with a different provider and then this is what happens - Appointment can't populate at all. I will work on it again in the morning as I have until noon.
The Specialty form and Specialty report don't work and I don't know why. As I said earlier today and also on Friday, the SubSpeciality works perfectly on its own in its own form and report. It's driving me nuts that I got SubSpecialty working but not Specialty. The Specialty report does populate from Navigation but not from the search.
I've tried everything. If you see the screen shot below, you will see I removed both Specialty and SubSpecialty and made them into their own forms - for Speciality, I have sfrSearchSpecialty as a form and rptResultsSpecialty as a report. For SubSpecialty, I have sfrSearchSubSpecialty as a form and rptResultsSubSpecialty as a report.
If I open the rptResultsSpecialty as a report from Navigation, it works and the report populates. But when I try to search from the sfrSearchSpecialty form, the report does not populate.
Option Compare Database
Option Explicit
Private Sub BtnResetSpecialty_Click()
Dim ctl As Control
For Each ctl In Me.Section("Detail").Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
Case acCheckBox
ctl.Value = False
End Select
Next
End Sub
Private Sub BtnSearchSpecialty_Click()
On Error Resume Next
Dim strWhere As String
Dim strSQL As String
Dim lngLen As Long
'Number field example. Do not add the extra quotes.
If Not IsNull(Me.cboSpecialty) Then
strWhere = strWhere & "([sSpecialtyID] = " & Me.cboSpecialty & ") AND "
End If
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
strSQL = strSQL
DoCmd.OpenReport "rptResultsSpecialty", acViewReport
DoCmd.Maximize
Else
strWhere = Left$(strWhere, lngLen)
strSQL = strSQL & " WHERE " & strWhere
DoCmd.OpenReport "rptResultsSpecialty", acViewReport, , strWhere
DoCmd.Maximize
Reports![rptResultsSpecialty].Filter = strWhere
Reports![rptResultsSpecialty].FilterOn = True
End If
End Sub
I'm frustrated and I'm sure you are too. Again I really appreciate it.
They only populate once you pick a provider. I had explained that if you want those other sorts to work independently then you need to make separate forms.
I went back to the email you sent, and the search results do not populate the report at all. That was what had happened on my home computer too. The Speciality and the Subspeciality boxes created under the Insurance do not even populate at all either. Maybe it got corrupted?
Let me revert to that version. I have a zillion versions by now. It didn't quite work right from my home computer but that is an Apple so maybe it'll work on this PC here at work.