Requery a form from a macro

[sardamil]

Hi,



I'm a complete novice to vba. I'm trying to automatically requery a form after executing a macro. I know there's a lot out there about requery, but I can't seem to get it to work. It's clear that the best way to do this, is to code it in vba. Can somebody help me with this?

I have a form called "Formulier_afspraak_nieuwe_patient" that I'm trying to requery from the converted macro Macro_toevoegen_nieuwe_patient()

Thanks for the help.

[June7]

What do you mean by 'can't get it to work'? Error message, nothing happens? Show code for analysis or provide project.

The requery needs to be in some event procedure. What you show looks like a function call.

[sardamil]

To be honest, I have no clue. I'm just trying to get a form to work to help out a friend. I was doing ok, till I needed to requery the form. As I have no knowledge of vba, I tried to find out how to do this, but didn't understand enough to work out how to do this in vba. I was hoping anybody could show me how to do this, so I can learn from that. Anyway, here's the code:

'------------------------------------------------------------
' Macro_toevoegen_nieuwe_patient
'
'------------------------------------------------------------
Function Macro_toevoegen_nieuwe_patient()
On Error GoTo Macro_toevoegen_nieuwe_patient_Err
DoCmd.SetWarnings False
' Toevoegen nieuwe patient
DoCmd.OpenQuery "Query toevoegen nieuwe patient", acViewNormal, acEdit
' Berekenen wachttijd
DoCmd.OpenQuery "Query berekenen wachttijd", acViewNormal, acEdit
' Toevoegen hoogste patientnummer aan tabel afspraak
DoCmd.OpenQuery "Query toevoegen hoogste patientnummer aan tabel afspraak", acViewNormal, acEdit
' Afspraak nieuwe patient toevoegen
DoCmd.OpenQuery "Query toevoegen afspraak", acViewNormal, acEdit
' DoCmd.Requery "Formulier_afspraak_nieuwe_patient"
Forms![Formulier_afspraak_nieuwe_patient].Form!Requery
Macro_toevoegen_nieuwe_patient_Exit:
Exit Function
Macro_toevoegen_nieuwe_patient_Err:
MsgBox Error$
Resume Macro_toevoegen_nieuwe_patient_Exit
End Function

[sardamil]

I forgot to mention that a query is linked to the form. I'm not sure whether you should requery the form or the query.
The query is: "Query selecteer laatste patient"

This statement results in a syntax errror message: Forms![Formulier_afspraak_nieuwe_patient].Form!Requery

[R_Badger]

I have taken the liberty of translating the vb from Dutch to English (this helps as often people name things hinting at what they plan to do)

Function Macro_add_new_patient()
On Error GoTo Macro_add_new_patient_Err
    DoCmd.SetWarnings False
    ' add new patient
    DoCmd.OpenQuery "Query add new patient", acViewNormal, acEdit
    ' calculate waiting
    DoCmd.OpenQuery "Query calculate waiting", acViewNormal, acEdit
    ' Add highest patient number to table appointment
    DoCmd.OpenQuery "Query Add highest patient number to table appointment", acViewNormal, acEdit
    ' Add a new patient appointment
    DoCmd.OpenQuery "Query Add a new patient appointment", acViewNormal, acEdit
    ' DoCmd.Requery "Form new patient appointment"
    Forms![Form new patient appointment].Form!Requery
Macro_add_new_patient_Exit:
    Exit Function
Macro_add_new_patient_Err:
    MsgBox Error$
    Resume Macro_add_new_patient_Exit
End Function

You are almost, and I suspect working code should be below...

Function Macro_add_new_patient()
On Error GoTo Macro_add_new_patient_Err
    DoCmd.SetWarnings False
    ' add new patient
    DoCmd.OpenQuery "Query add new patient", acViewNormal, acEdit
    ' calculate waiting
    DoCmd.OpenQuery "Query calculate waiting", acViewNormal, acEdit
    ' Add highest patient number to table appointment
    DoCmd.OpenQuery "Query Add highest patient number to table appointment", acViewNormal, acEdit
    ' Add a new patient appointment
    DoCmd.OpenQuery "Query Add a new patient appointment", acViewNormal, acEdit
    ' requery statement
    [Forms]![Form new patient appointment].Requery
Macro_add_new_patient_Exit:
    Exit Function
Macro_add_new_patient_Err:
    MsgBox Error$
    Resume Macro_add_new_patient_Exit
End Function

Sorry but I am too lazy to translate it back to Dutch, but you should be able to get the idea.
Like I said in your previous post regarding this you are likely to be better off binding it to the form control direct in VB, and running everything from VB, rather than relying on macros.

[sardamil]

Thanks. That solved my problem.
You're not lazy btw. You helped me solve my problem.
I'm not sure how I would bind it to the form, but it works now. Perhaps I can find out how to bind it to the form later.