Forms & Sub Form Problems

[Sundance]

I need to do the following:<br>
<br>
Have field Type<br>
Type Description<br>
Beginning Date<br>
Ending Date<br>
Access Sub Form using Beginning Date & Ending Date as date identifier for clear date.<br>
Sub form has <br>
<br>
Clear Box Tape Number Retention Clear Date etc..<br>
<br>
The problem is, I need to have the system prompt for Type<br>
Type Desc, Beginning Date & Ending Date, before displaying data in the Sub form. Please Help!!

 

[bitbrain]

Sub forms are usually used for one-to-many relationships. For example; If you had a Customer table and an Order table, the Customer information would be on the main form and the Customer's Orders would be in the sub form.<br>
<br>
Is this your situation? Do you have two tables? If so, which fields are in which tables?<br>
<br>
If this isn't your situation, sub forms may not be the answer.<br>

 

[Sundance]

Type is also part of the subform<br>
<br>
Query created from Type info, Beg Date & Ending Date Added when the query created<br>
<br>
Header Information Query Type_Clear, param's "Type" passed from another form display all types, passing works fine.<br>
*************************************************<br>
Type ______ <br>
Desc ___________________<br>
Beg Date 01/01/99<br>
End Date 06/25/99<br>
<br>
Sub Form = Tape_History<br>
**************************************************<br>
Clear Tape Retention Clear<br>
Box Type Number Description Date<br>
__ ______ ______ ___________ 99/99/99<br>
<br>
***************************************************<br>
The relationship is Type Query "One" to Type History "Many"<br>
<br>
The problem is, I need to have the system prompt for Type<br>
Type Desc, Beginning Date & Ending Date, before displaying data in the Sub form. Please Help!!

 

[bitbrain]

What are the "key" fields? Is it simply Type? Do you want to display data for the specific type that was passed or do you want the user to look-up/enter a new type and retrieve the detail?<br>
<br>
Is this a retrieval only form or do you want to add/update as well?

 

[Sundance]

Type is the key field, yes, tape number is also a key, but I want all tapes with that type, using beg date & end date as the between reference.

 

[bitbrain]

The query can reference form fields. So, make sure the form's controls (text boxes etc.) have meaningful names. Bring the query up in design view. On the Type column click the criteria cell to give it the focus and click the Build button. The Expression Builder dialog box appears. Double click Forms & select your form. Control names appear in the middle column. Find your key (Type) control and double click it. Click OK. Now your query uses a form field as part of its criteria.<br>
<br>
When the form is opened, the query will use the field. If the field is blank, no results will be returned. Not sure how your form works - you said that the Type field is passed as a parm. If this is so, the query should retrieve data. You can always add a button to refresh the form. Have the button's click event execute a macro with the Requery action.<br>
<br>
Alternatively, add a combo box to look-up Valid types from a table. Have the combo-box's click event execute the requery macro.<br>