Initial Form View to Display Null Values

[Birky]

I have an issue where I can’t figure out how to open a form where all the objects within are nulled out until a user selects an appropriate Project Name. My form has several objects (12) within which are pulling info from my Custom Code table. I also have two sub-forms that are tied to two other tables within my database. I have everything working, for if you were to select and or update information it is properly updating the form and or tables accordingly. The problem I have is when the form is initially opened, I can not get all the objects to be blank? When it opens the first row within my table is being displayed. I’d like to have this form open with NULL values until a user selects a Project Name where it then displays the appropriate information from my three tables. I’m hoping I am missing something very basic here but I have been working on this one for two days straight and I can’t figure out how to do it.

Any help would be greatly appreciated.

Birky

[Rabbit]

What about just moving the form to a new record when it loads?

[ADezii]

It would be a very simple matter to enter Null Values in all Fields when a Form initially opens. The only problem with that is the Null Values would be written to every single Field in the 1st Record - not exactly what you would want. There is, however, an unorthodox solution:

1. Make an exact copy of your Form (CTL+C / CTL+V).
2. Set the copy's RecordSource to Null.
3. Set the ControlSource of every Control on the copy to Null.
4. Set the Navigation Buttions to No.
5. What you have now is an exact duplicate of the original Form except that it is Unbound, all its Controls have no Control Sources, and the Form itself has no means of navigation. All Fields on the Form will be blank and probably should be Locked to eliminate any possibility of data entry.
6. Once the User selects a Project Name from the copy, open the 'real' Form based on this selection.
7. As stated, it is a little unorthodox but it definately will work until someone comes up with a better solution.

NOTE: Rabbit's idea would be more practical as long as you don't care if the User navigates backwards and displays actual data prior to making a selection from the Project Name Drop Down.

[Birky]

How do I set it to a new record set when the form opens?

Is it possible to make an unbound form become a bound form once a record is selected?

Thanks for the help on this, I sure do appreciate it.
Birky

[Rabbit]

I assume the project data form is a subform? And that the project drop down list is on the main form? If not perhaps you should set it up this way. Just have the subform remain invisible until they make a selection in the drop down box.

[Birky]

All,

After consulting with my supervisor we believe the option to duplicate the form with disabled object is the route we want to pursue. On that note I have created a copy of the form and have disabled everything except the Project_Name (Combo20) which the user will have the ability to select from a drop down. After the Update of this combo box I have the real form opening which I want to display all the pertinent information for the Project Name (Combo20) they had selected from the copied form (which I hope to be closing after the real form has been opened and has passed the Project Name variable to the real form). Can you help me pass the variable to the new form so the pertinent info is then displayed?

Here is my code from the After Update event of the Copy_Update_Custom_Code form.

Private Sub Combo20_AfterUpdate()
On Error GoTo Err_Combo20_AfterUpdate
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "Update_Custom_Code"
'    DoCmd.Close
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Exit_Combo20_AfterUpdate:
    Exit Sub
 
Err_Combo20_AfterUpdate:
    MsgBox Err.Description
    Resume Exit_Combo20_AfterUpdate
 
End Sub
 

Here is the code I have behind my After Update event in the real Update_Custom_Code form. How do I pass the variable that was selected in the Copy_Update_Custom_Code form to this real Update_Custom_Code form? Note I do no have to close the Copy_Update_Custom_Code form, I can just hid it. Does that make it easier to pass the variable from one form to the other?

Private Sub Combo20_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object
 
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Project_Name] = '" & Me![Combo20] & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

[Rabbit]

I don't know why you're doing it this way.

Why not just reenable everything on the form when they choose something from the list? Or use the main form subform route?

[Birky]

This is because even though the objects are disabled they show values within for the first record set of the table. We would like none of the objects filled in when the report is initially opened.

[Rabbit]

You could just set them to be invisible and then make them visible after.

[ADezii]

All,

After consulting with my supervisor we believe the option to duplicate the form with disabled object is the route we want to pursue. On that note I have created a copy of the form and have disabled everything except the Project_Name (Combo20) which the user will have the ability to select from a drop down. After the Update of this combo box I have the real form opening which I want to display all the pertinent information for the Project Name (Combo20) they had selected from the copied form (which I hope to be closing after the real form has been opened and has passed the Project Name variable to the real form). Can you help me pass the variable to the new form so the pertinent info is then displayed?

Here is my code from the After Update event of the Copy_Update_Custom_Code form.

Private Sub Combo20_AfterUpdate()
On Error GoTo Err_Combo20_AfterUpdate
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "Update_Custom_Code"
'    DoCmd.Close
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Exit_Combo20_AfterUpdate:
    Exit Sub
 
Err_Combo20_AfterUpdate:
    MsgBox Err.Description
    Resume Exit_Combo20_AfterUpdate
 
End Sub
 

Here is the code I have behind my After Update event in the real Update_Custom_Code form. How do I pass the variable that was selected in the Copy_Update_Custom_Code form to this real Update_Custom_Code form? Note I do no have to close the Copy_Update_Custom_Code form, I can just hid it. Does that make it easier to pass the variable from one form to the other?

Private Sub Combo20_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object
 
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Project_Name] = '" & Me![Combo20] & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Try this on for size:

Dim stDocName As String
Dim stLinkCriteria As String
 
stDocName = "Update_Custom_Code"
stLinkCriteria = "[Project Name] ='" & Me![Combo20] & "'"
 
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "Copy_Update_Custom_Code", acSaveNo

[Birky]

All,

Thank you very much for this is perfect when using a copy of the form (for input) and then passing the variable to the new form before closing. This is exactly what I was looking for. Although, they had changed some things on me and I believe I found a cleaner way to do the same thing with only one form. If I open the form in data input mode and disable all the objects except for the one I need to key off of. Once that object is populated I can then change the form to no data input and re-enable all the objects . This too works out well and has proven to be a supervisor pleaser.

Again thanks for all your help for you all had helped me think outside the box.

Birky

[OxfordConsult]

Hello,

I am trying to do exactly the same thing, however having a copy of the form is not an option. I am intrigued by rabbits idea of making the subform become visible only after a selection has been made in the project box (in my form it displays a list box).

Any help would be greatly appreciated.

Oxford