IF statements to Open Forms (Access 2003)

[Garyn]

Hello!

I am quite new at writing VBA and appreciate any help that I could get. I can usually troubleshoot the basic needs of my database, but this one was a little beyond my current skills.I am using Access 2003 and working with related records between two forms (picture attached). The first (pictured) form is an App form that links to a Classes form through the Class List button.

My problem is that if the user clicks the 'Class List' button before an App is created, then it produces a Run-time 3075 error because it can't find a related record.

What I want to to is make an If statement that: 1) If record has matching record, simply open the form (what happens now) and 2) If no matching record, First creating a record in the current form, and then open the second form.

Thanks for any help!

 

[MStef]

What are your tables look ?
What are the PRIMARY KEYS in this tables ?
What is the FOREIGN KEY in Clas data ?
What is the RELATIONSHIPS ?

 

[Garyn]

What are your tables look ?
What are the PRIMARY KEYS in this tables ?
What is the FOREIGN KEY in Clas data ?
What is the RELATIONSHIPS ?

Thanks for the reply!

My table relationships are Clients -> Applications (on Employee ID) -> Classes (on App ID). My primary key between Applications and Classes is App ID.

First I load the Client form and from there load the App form. If the Client has no Apps, it the form loads and just has blank data. However, if I try to click for the Classes form without any application, it cannot find the primary key (App ID) and so it forces the 3075 error.

 

[MStef]

If your RELATIONSHIPS is CLIENTS on side 1 and APPLICATIONS on side many,
in that case you must enter CLIENTS before APPLICATIONS record.
In that case the CLIENTS form is a MainForm, and APPLICATIONS form is a subform.
If your RELATIONSHIPS is APPLICATIONS on side 1 and CLIENTS on side many,
in that case you must enter APPLICATIONS before CLIENTS record.
In that case the APPLICATIONS form is a MainForm, and CLIENTS form is a subform.
Populate "LINK CHILD FIELDS" and "LINK MASTER FIELDS" properties for subform.

 

[Garyn]

If your RELATIONSHIPS is CLIENTS on side 1 and APPLICATIONS on side many,
in that case you must enter CLIENTS before APPLICATIONS record.
In that case the CLIENTS form is a MainForm, and APPLICATIONS form is a subform.
If your RELATIONSHIPS is APPLICATIONS on side 1 and CLIENTS on side many,
in that case you must enter APPLICATIONS before CLIENTS record.
In that case the APPLICATIONS form is a MainForm, and CLIENTS form is a subform.
Populate "LINK CHILD FIELDS" and "LINK MASTER FIELDS" properties for subform.

Thanks for the help!

My relationships are 1 to many on the primary keys (Employee ID for Clients-Apps and App ID for App-Classes).

My first form is "Clients" which then can open an "App form" with has "Clients table" in the mainform (to link to specific record) and "App table" in the subform. From the "App form" you can click to open a different form window with "Classes." This all works perfectly fine if there is an App ID already assigned... however if there is not one, it does the runtime error. All I am looking to do is make an IF Statement for how to check if there is already an App ID assigned before opening up a new window. I have been checking around but I can't seem to figure it out myself. A few things theoretically I thought might work, but I don't know how to code them:
1) Check if there is a corresponding App ID, if not than create an App ID, and then open Classes form
2) Check if there is a corresponding App ID, if not than put today's date for App Date (which would force a new App ID to be made), and then open Classes form
3) Just do nothing if there is no App ID (but if there is, then open the Classes form)

 

[Garyn]

I decided to just go with the third option an put an Error Trap in there to do nothing. I'd still like to do 1 or 2, but it is beyond my skills, and the 'do nothing' for the Error Trap is fine for now. Thanks again!

 

[Garyn]

I decided to just go with the third option an put an Error Trap in there to do nothing. I'd still like to do 1 or 2, but it is beyond my skills, and the 'do nothing' for the Error Trap is fine for now. Thanks again!

Actually, I just used the Error Message to cover it and allows me to make a new App by clicking the Classes button. Here's the code I used.

Private Sub Classes_List_Click()
On Error GoTo Err_Classes_List_Click

        DoCmd.RunCommand acCmdSaveRecord
        strWhere = "AID = " & Me.AID
        DoCmd.OpenForm "Classes Popup", , , strWhere

Exit_Classes_List_Click:
    Exit Sub

Err_Classes_List_Click:
    Me![Application Date] = Date
    DoCmd.RunCommand acCmdSaveRecord
    strWhere = "AID = " & Me.AID
    DoCmd.OpenForm "Classes Popup", , , strWhere

End Sub

It inputted today's date for the App Date, so it created a record, therefore having the App ID to link to the Classes' form. Is there any problems with that code?

 

[tehNellie]

Depending on what me![application date] actually is:

Private Sub Classes_List_Click()

        if me![application date] = "" Then 'assuming this is a text box, "" might not be a valid test.
       
           me![application date] = date

        end if

        DoCmd.RunCommand acCmdSaveRecord
        strWhere = "AID = " & Me.AID
        DoCmd.OpenForm "Classes Popup", , , strWhere

Your error handling runs a (tiny) risk that something else triggers the error handling, overwrites the date that is present in that field? and then opens the form.

You could of course trap the specific error number, but why duplicate code when you don't have to?

I try to reserve error handling for something unpredictable/unavoidable. This, to my mind, doesn't fall into that category. You know it can occur, what causes it and what needs to happen to prevent the error happening to begin with so test for the condition that can cause the problem and fix it before it occurs.