Microsoft Access 2007 Hiding Subforms when Main Form Does Not Have Rec

[ProgEvalJAA]

I have an Access 2007 database with four tables, and I’m struggling with some of the subforms linked to those tables.

Below I've tried to outline my structure.

The first table is my main table and contains a primary key field of id number for each record. For example, id number # 12345 serves as the primary key field for a particular record for a particular customer such as John Doe.

The second through fourth tables are linked to the id number and are similar to the following:

Providing Company
Attending Company
Volunteers

I have a main form called Worksheet. It is linked to the main table and has a dropdown box forcing the user to select a particular customer id number such as # 12345.

The main form has three subforms (one for each of the other three tables: Providing Company, Attending Company, Volunteers) linked on id number by the master/child field settings.

My question is this:

I have purposely placed a record in the main table with an id number of 0. When the main form (with accompanying subforms) first displays, it comes up with that record and shows the id number of 0. However, it allows my users to save data on the subforms under that id number. I would like the subforms to be hidden and not allow any data entry for that id number of zero. All the other id numbers should display and allow data entry. What is the easiest and most efficient way to accomplish this desired outcome?

 

[PHV]

In the Current event procedure of the main form:

Dim bFlag As Boolean
bFlag = Not (Me![id control] = 0)
Me![Providing Company subform control].Visible = bFlag
Me![Attending Company subform control].Visible = bFlag
Me![Volunteers subform control].Visible = bFlag

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ProgEvalJAA]

Thanks for the quick reply. I appreciate it.

I have very little experience in writing event procedures, so forgive me for asking some what probably seem a few extremely basic questions.

1) To be clear, is the Current event procedure the same as the On Current event procedure? I'm working in Access 2007.

2) When you refer to [id control] in the second line of the code, do I change the name inside to be the name of the field (for example, id number)?

 

[PHV]

1) choose <event procedure> and click the ellipsis (...)
2) the name of the control bound to the field

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ProgEvalJAA]

I'm getting closer.

I am in the On Current Event procedure, and I have placed the code in the database as follows:

Private Sub Form_Current()
Dim bFlag As Boolean
bFlag = Not (Me![ID] = 0)
Me![Providing Company ID].Visible = bFlag
Me![Attending Company ID].Visible = bFlag
Me![Volunteers ID].Visible = bFlag

End Sub

Access gives me a runtime error message # 2465 that says it cannot find Providing Company ID.

Any additional thoughts?

 

[PHV]

You have to use the name of the controls hosting the subforms.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ProgEvalJAA]

Thank you, with your help and a little help from my supervisor, I have solved this problem.

Thank you so much!

 

[ProgEvalJAA]

I now have another problem. The code performed beautifully, and the form/subforms behaved predictably until I compacted/repaired the database. Then, I received an error message that reads:

*The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure]. *There may have been an error evaluating the function, event or macro.

Any thoughts as to how to correct this matter?

 

[ProgEvalJAA]

Well, I performed the compact and repair again, and the form is behaving again. I've tested it several times after closing the database at least six times. Then, I had a coworker test it, and it works fine. At this point, I cannot explain it.