CODEWITHSUNDEEP
vbams-access
Gessak
Gessak

Reputation: 35

Getting a form to use multiple queries as record source

So I've been working on the next step of my program, which consist on two buttons each of one is supposed to open a treatment form based on data that is asked to the user.

After searching a bit on the internet i found that the following syntax should have made the trick 

Forms!FormName.RecordSource = Query

It didn't.

My actual code is as follows:

Private Sub BQueryDate_Click()
On Error GoTo Err_BQueryDate_Click

Dim stDocName As String
Dim stLinkCriteria As String

Forms!ShowTreatment.RecordSource = QueryDate
stDocName = "ShowTreatment"

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_BQueryDate_Click:
Exit Sub

Err_BQueryDate_Click:
    MsgBox Err.Description
    Resume Exit_BQueryDate_Click

End Sub

"Showtreatment" used to be a form that showed every record for the table Treatment, but now I want to switch between the queries QueryDate and QueryHospital so I can sort the data.

The thing is that with that code when I click the button access tells me that "ShowTreatment" is not found on the Form list.

I think that it can be caused by the recordsource line not being on the right place, but couldn't made it to work anyway, and I did not found any example of it applied to my concrete case since every one I found was trying to use it on a subform, and I need to change a normal form.

Edit: Ok, dumb question cause I found something similar in here. (If only I had searched for 3 pages more)

Access VBA: Set record source of form on button click

I'll give it a try.

Nope. Now it just ask me for date AND hospital name. I think I'm missing something important here.

The code 

Forms.Item(MostTratamiento).RecordSource = "ConsultaQueryHospital"

Doesn't give me an error message (it even ask me about the hospital name) but doesn't change the record source either so it just shows the default.

Upvotes: 1

Views: 1155

Answers (1)

jacouh
jacouh

Reputation: 8769

Like this:

Private Sub BQueryDate_Click()
  On Error GoTo Err_BQueryDate_Click

  Dim stDocName As String
  Dim stLinkCriteria As String

  stDocName = "ShowTreatment"

  ' we open first the form:
  DoCmd.OpenForm stDocName, , , stLinkCriteria

  ' we then set up it's RecordSource:
  Forms!ShowTreatment.RecordSource = "QueryDate"

Exit_BQueryDate_Click:
Exit Sub

Err_BQueryDate_Click:
    MsgBox Err.Description
    Resume Exit_BQueryDate_Click

End Sub

Upvotes: 1

Related Questions