Linking Combobox back to table

[jzwp11]

The SELECT DISTINCT clause forces only unique values to show. The criteria (WHERE clause) is what filters the values being supplied to the combo box based on the choice in the previous combo box. The other key is to make sure you reference the name of the combo box correctly in the After Update event of the combo box. For example, in the first combo box in your sample database, it had two statements that both referenced combo4, but combo4 was not a combo box control on the form. You have to correctly reference the next combo box in the sequence.

[jlclark4]

Here's my last crazy question. How can I make the default view a new record instead of record #1?

[jzwp11]

You can change the data entry property from no to yes. Then any time you open the form, it will open to add a new record. The downside is that you cannot view already existing records when the data entry property is set this way.

[jlclark4]

Yeah, I knew that option, but I need the ability to still view additional records. I may just train the team to automatically click Add Record on first entry and create a new one. We will just have to see!

[jzwp11]

...or you can present your users a selection form that has a couple of buttons ("Add New" or "Edit Existing") and change the data entry property as the form loads depending on their decision.

[jlclark4]

That my friend is a brillant idea. And I think I actually know how to do that! Up until the opening it in two different ways comes into play. We shall see.

[jlclark4]

Yes, I am going to need help on this. I have the first Command button (CmdSearch) set to access the entire form to search through. The second button (CmdAddNew) needs to be set so the default is a new record. What is the best way to go about this? Below is the current VBC

Option Compare Database
 
Private Sub CmdSearch_Click()
On Error GoTo Err_CmdSearch_Click
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "FmInitial"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Exit_CmdSearch_Click:
    Exit Sub
 
Err_CmdSearch_Click:
    MsgBox Err.Description
    Resume Exit_CmdSearch_Click
    
End Sub

[jzwp11]

Using the command button wizard to open a form, you can modify the code that is generated to set the data entry property.

So in the On Click event of the command button to open the form ready to enter a new record, the code would look like this, the only thing I added was the line shown in blue

    
Private Sub Command1_Click()
On Error GoTo Err_Command1_Click
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "NameOfFormToOpen"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    Forms!NameOfFormToOpen.DataEntry = True
    
Exit_Command1_Click:
    Exit Sub
Err_Command1_Click:
    MsgBox Err.Description
    Resume Exit_Command1_Click
    
End Sub

You would have similar code behind the button to open the form to view/edit all records by just toggling the True to False

[jlclark4]

Wow, simple enough. Thank you!

[jzwp11]

You're welcome!

[jlclark4]

I'm Back

The database was a success. Management only had a few layout changes. Do have another question. With the combo boxes, can you set controls to become visible for only certain selections?

I would like the Compliance to become visible only if Written or Email is selected from the Method of Reciept and Yes is checked for the Official Complaint. Is this possible? I would need Official Complaint to be visible at all times.

[jzwp11]

Yes, that is possible (see attached). The only thing is that you must make sure that the user goes in the appropriate order from the first combo box to the option group selection. So it might be better to hide the option group until something is chosed from the initial combo box.

[jlclark4]

I like how that looks, and I agree with the hiding of the option group until the first combo box has been selected. Thanks for your help once again!

[jzwp11]

You are welcome!

[jlclark4]

Hey

I'm back again. I am trying to calculate the amount of point a question is worth based on the answer chosen in the Frame.

For example:
I have Frame 19 with Option 22 (Y), 24 (N) and 26 (N/A). If Option 22 is selected, I would like the 3 to appear in text574. If Option 24 or 26 are selected, I would like 0 to appear.

In some cases, Y could equal 2 or 1 point(s).

What should be done to get this to work? Am I overlooking something?