Display One of Two Forms depending on query results using OnClick event of a button

[MsAdams]

Not sure that the title best describes what I am trying to do (or that this is the right section of the forum) so I will provide as much detail as possible. This is my first post here!

Using Access 2003 currently. My database tracks practice exams for employees. There is a table that contains the exam data and a table that contains the employeed data (one-to-many relationship between employee > exam tables).

I have a dashboard form that is displayed when users open the database. There is a button on the dashboard that allows a user to search by a specific employee ID via a pop-up box (criteria) that drives a query. The data about the employee is displayed in the top of the form [query against the Employees Table]. The bottom of the form contains a subform with the exam detail [query against the Exams Table] that exists for that employee. The tables are linked by the Employee ID.

What I am trying to do (and am totally stumped on the best way to approach it) are the following:

1. Hide the subform if there is no exam data for the employee ID entered in the pop-up box - I tried the 'can shrink' property but it didn't work and subsequently....

2. If there is no exam data for the selected employee, possibly display a different form (identical to the existing form but with a different background color) with two new fields/controls (Checkbox and date field) and no subform.



When this new form is displayed and the a) box is checked and b) a date is entered in the new date field, these two fields in addition to the employee ID are saved to a new, separate table that I can run a query/report against later.

I am not new to Access, and am somewhat familiar with modifying VBA code to accomplish more complex tasks, but this change has completely got me stumped. Any assistance would be appreciated!

[June7]

What do you mean by popup box? Is this InputBox function or query parameter prompt (I NEVER use them) or a form?

1. Set Visible property of the subform container control.

2. Sounds doable. Attempt code, provide for analysis if you have issue.

[MsAdams]

Yes, I am using the query parameter prompt. I know it's not ideal, but it was the quickest option at the time when I developed the database.

1. Set Visible using VBA IF statement ?

2. I will make an attempt or find something similar to what I am trying to do - but honestly I don't even know where to begin as far as the code. I suppose I would start with creating the duplicate form and adding the additional fields, etc. Then work on figuring out when to display which form. Will post here with follow-up.

Thanks.

[June7]

1. Yes, use a conditional code (I use only VBA)

2. Don't really understand why need a different form but can certainly be done. More conditional code. The real trick is figuring out what event(s) to put code in.

[MsAdams]

For #1, can I use an IF statement to hide the entire subform or do I need to hide each control? I don't want the subform to display at all if there are no records.

For #2 I don't necessarily need a different form, but we want to make the form without the subform visibly different from what users see when there is data in the subform. I also have had trouble in the past getting fields in a form to write to a table that the form is not 'linked' to so any guidance that can be provided for that piece of what I am trying to do would be helpful.

I am sure there is some conditional formatting that can be done, but I would not know how to do that programmatically. I am somewhat of a novice when it comes to the VBA coding aspect of access.

[June7]

1. Code hides the subform container control, then the form held by the control is not visible.

2. Code could set visibility of controls depending on some condition. Also, control's Conditional Formatting can set it enabled/disabled.

Saving data from form to another table could use SQL INSERT or UPDATE actions, in VBA code.

[MsAdams]

I actually went about this a different way (very similar to your most recent post, June7) and was able to reach the same goal. I used the Tab Control in the main form, and put each of my subforms on a tab. I used the following VBA code on the OnLoad event of the Main Form to hide/display the tabs with the subforms based on conditions:

Private Sub Form_Load()
Dim ctl As Control
Set ctl = Me!history.Form!Exam
Me!tabHOLD.Visible = (IsNull(ctl) Or Len(ctl & "") = 0)
Me!tabHIST.Visible = Not (IsNull(ctl) Or Len(ctl & "") = 0)
End Sub

It worked perfectly - hopefully this might be useful to someone else trying to do something similar!

Since I am using parent/child forms when the subform is updated in the HOLD tab, it updates my table automatically witout having to use any kind of update/append query. Thanks for your help and patience!