Help! Forms in switchboard

[GeorgeChr]

I have some forms created by parameter queries (the ones whith the pop up window) and everything work perfectly but when I put these forms into a switchboard I can't see the prompt window and therefore I can't use them. I've tried to figure out what's going on but I can't since I'm an amateur in access. Does anyone know what to do??

 

[Guus2005]

One way of doing this:

Use functions to open your forms.
Build a query for your form, save the query, open the form based on this query.

Put these function names in your switchboard.

Enjoy!

 

[GeorgeChr]

More help

One way of doing this:

Use functions to open your forms.
Build a query for your form, save the query, open the form based on this query.

Put these function names in your switchboard.

Enjoy!

Thank you for the help Guus but I still have onw more question since I'm not too familiar with VBA. What is the name of that function (if it has any..) and do i have to write a code in SQL (if yes, you know where i can find this code to paste it?) or is it a "normal-way" to do it? Thanks again!

 

[Guus2005]

VBA code

Create a function in a module. It should look someting like this:

Public Sub [COLOR="Red"]OpenFormCustomers[/COLOR](lngCustomerID as long)
    
    Dim frm As Form
    Dim strSql as string

    strSql = "Select * from Customers Where ID = " & lngCustomerID
    MakeQueryDef "qryCustomer", strSql

'The form frmCustomer should be bound to qryCustomer!
    DoCmd.OpenForm "frmCustomer"

End Sub

You need these functions also.

Public Function MakeQueryDef(strSQLname As String, strSQLdef As String) As Boolean

   Dim qdf As QueryDef
   Dim dbs As Database

   On Error GoTo Err_MakeQueryDef

   Set dbs = CurrentDb

   DeleteQueryDef strSQLname ' Deleting Query when it exists

   Set qdf = dbs.CreateQueryDef(strSQLname, strSQLdef)

   MakeQueryDef = True

Exit_MakeQueryDef:
   Exit Function

Err_MakeQueryDef:
   MakeQueryDef = False
   MsgBox "Error in MakeQueryDef : " & Err.Number & ":" & Err.Description, vbExclamation, "Error while making query definition."
   Resume Exit_MakeQueryDef
End Function
'
Public Sub DeleteQueryDef(strSQLname As String)

   Dim dbs As Database

   Set dbs = CurrentDb

   On Error Resume Next 'Ignore error message when query doesn't exist
   dbs.QueryDefs.Delete strSQLname

End Sub

The name of that function (currently marked in red) should match the one you enter in the switchboard.
(btw, this code was not compiled so it may not be completely correct.)

Good luck!

 

[GeorgeChr]

re

thnx guus, ill try to work it out! Hope I don't mess my entire DB!

 

[GeorgeChr]

Guus I have some questions for you. I've done the following: Opened a module, pasted the code you gave me, changed the name of the form as you told me but there is a problem with that.. the name of the form is:"contacts which have email". when i leave space between the words there is a problem and same when i don't. What should i do?
Second, i have a already a query whith the same name as the form in which the form is based, should i re-create one?
third, after i create the function you said, how it will co-operate with my form? i dont understan anything here!! sorry if im a pain in the ass but im very confused..

 

[Guus2005]

No worries

if you have a form namend form1, a module which contains the functions makequerydef named module1 and a query on which your form was based named query1, you can let them all work together. I prefer all the names of my objects without spaces. Sometimes Access agrees with me. You can't create a modulename with a space in it. Hence: Module1 or Module_1 or AapNootMies.

I provided a sample database for you.

Enjoy!