How to change combobox query on click?

[ittechguy]

I have a combo box on a form (which is bound to tblEquipment table). The combobox is bound to tblIPAddressFK which is a number field linking to the primary key in tblIPAddress. The RowSource for this combo box is a simple query containing two fields, the PK and the IP Address (text). The purpose of this is to display the text IP address field, while being able to modify the FK in tblEquipment.

The problem I have is I need to view current records, as well as edit records using the same controls. For all the other combo boxes, this approach has worked well for me. I use the same controls to view records as I do to edit them. Additionally, I can change the form to data entry mode and use the same controls to add a new record. The if statement is kinda long, but it works.

The problem with this here is that whenever I need to add a new record or edit an existing record, I need to click on the combo box and see IP addresses that are not already in use. I can do this by changing the query to include a LEFT OUTER join to tblEquipment, adding tblIPAddressFK WHERE IS NULL and then the combo box will only display IP addresses that are not already in use.

However, if I use this query and am viewing a record in tblEquipment which already has an IP address in the tblIPAddressesFK field, the combo box will not display anything in them.

So, basically. I think I need to have two RowSource queries... one to view data, and another one for whenever the combobox is clicked. Is this possible?

An alternative, I think, would be to create the query that filters out IP addresses already assigned to equipment. But then, add one value to the combo box for the ip address assigned to the equipment which is being viewed on the form.

[NeoPa]

This really depends on exactly what you want to do.

If new records only need to be assigned new and unused values, but existing records can never be changed, then you can set the .ControlSource of the ComboBox in the Form_Current() event procedure. You could use the .NewRecord value to determine which to use for that record, but remember that, once saved, that same current record changes what it needs.

If you need existing records to be able to show their current values, but also be able to change them to new and previously unused values, then you set it up in the design and use your second idea.

PS. If your third paragraph is an accurate reflection of your requirements then your later idea will be required.

[jforbes]

I think there was another question a couple months ago that was very similar to this, but I couldn't seem to find it, so...

I think you can do what you want in the Where Clause of the RowSource for the ComboBox. You can include the restriction of unused IP Addresses along with including the IP Address currently stored on the Record.

This is an example that uses a Project/Employee scenario. Only Employees that are enabled are available in the DropDownList, but the currently selected Employee is also included regardless whether or not they are Enabled:

SELECT Employee.EmployeeID, Employee.Name, Employee.Enabled 
FROM Employee 
WHERE [Employee].[Enabled] <> 0 
Or [Employee].[EmployeeID]=[Forms]![EmployeeProject]![EmployeeID]

The Form will also need the following code so that the DropDownList is refreshed to include the current EmployeeID whenever the Current Record changes:

Private Sub Form_Current()
    Me.cboEmployeeID.Requery
End Sub

In the above, the Employee of the Current Record will always be included in the ComboBox DropDownList, so it will always display correctly.