So the Meter combo box (drop down) initially shows all Meter records, but when a check box is checked true you want the combo box to only display Meter records related to the MeterID of the current record on the main form?
You can reset the Row Source of the combo box in the After Update event of the check box. Example (air code);
Code:
Private Sub chkYourCheckBox_AfterUpdate()
If chkYourCheckBox Then
Me.cboYourComboBox.RowSource = "Select Meter From tblMeters " _
& "Where MeterID = " & Me.MeterID
Else
Me.cboYourComboBox.RowSource = "Select Meter From tblMeters"
End If
End Sub
You may also want to repeat this code in the Current event of your Form.
I have 1 form, with a Contract tab. With ContractID as PK.
Then I open a METER form and when I begin entering details the link between the Contract and Meter is made. So a FK ContractID is stored in the Meter table.
On the Meter form, I select from a list of Meter Refs, currently just show all meter refs.
What I need it to show is only Meter Refs that relate to that ContractID.
Can you post an example of the code you attempted?
Then I open a METER form and when I begin entering details the link between the Contract and Meter is made. So a FK ContractID is stored in the Meter table.
What do you mean by the join is made when you check the box? What join?
Also, in your first post you were talking about a Meter table, now you're talking about a Price table. Did something change? It's hard to give you advice if we can't follow what you're doing.
Can you post an example of the code you attempted?
Are we talking about a form with a sub form here? Two separate forms?
What do you mean by the join is made when you check the box? What join?
Also, in your first post you were talking about a Meter table, now you're talking about a Price table. Did something change? It's hard to give you advice if we can't follow what you're doing.
I have a form CLIENTS with 3 subforms. One of which is called CONTRACT on the contract tab (subform) I have a button to open up a new form to enter PRICES that relate to that CONTRACT.
The code for the button to open the PRICES form is:
Code:
Private Sub cmd_OpenCosts_Click()
On Error GoTo Err_cmd_OpenCosts_Click
DoCmd.OpenForm "frm_CostsPrices", , , "[contractID]=" & Me.Contract_ID, , , Me.Contract_ID
Exit_cmd_OpenCosts_Click:
Exit Sub
Err_cmd_OpenCosts_Click:
MsgBox Err.Description
Resume Exit_cmd_OpenCosts_Click
End Sub
Then on the PRICES form I have this code:
Code:
Private Sub Form_BeforeInsert(Cancel As Integer)
On Error GoTo Err_Handler
If Me.OpenArgs & "" <> "" Then
ContractID = Me.OpenArgs
DoCmd.RunCommand acCmdSave
End If
Exit_Err_Handler:
Exit Sub
Err_Handler:
MsgBox Err.Description
Resume Exit_Err_Handler
End Sub
When I enter data into the first field. The codes generates the ID from the Contract form and stores it in the PRICES form (table also). So each PRICE now links to a CONTRACT.
What I need to do is filter my dropdown to only show METER refs (each METER Ref relates to a contractID and are stored in a table called METERREFS).
But I cant filter it, as I have no Contract ID to say "hey, pull all the meter refs that match this ContractID".
So my idea was to have a field I enter into first (eg. a checkbox), then when you check the box, that ID is passed from CONTRACT to PRICES form, so the Combo can say "hey, bring me all meter refs that match that ContractID".
In the Meter table I have MeterID (PK), ContractID (FK) MeterRef
In the Contract table I have ContractID (PK), ClientID (FK), Contract Name etc..
In the Prices table I have PriceID (PK), ContractID (FK), Price etc..
I'm not sure, but I think from the above that the Meter combo box is on your Prices form. You're already passing the Contract_ID to the Prices form via OpenArgs, so just use that value to set the Row Source of your combo box in the Open event of the Prices form;
Code:
Private Sub Form_Open(Cancel As Integer)
If Nz(Me.OpenArgs, vbNullString) <> vbNullString Then
Me.cboMeters.RowSource = "Select MeterID, ContractID, MeterRef" _
& " From tblMeters Where ContractID=" & Me.OpenArgs _
& " Order By MeterRef;"
End If
End Sub
You'll need to adjust the above example to match your actual object names, and depending on the data type of ContractID, you may need to add delimiters.
I'm not sure, but I think from the above that the Meter combo box is on your Prices form. You're already passing the Contract_ID to the Prices form via OpenArgs, so just use that value to set the Row Source of your combo box in the Open event of the Prices form;
Code:
Private Sub Form_Open(Cancel As Integer)
If Nz(Me.OpenArgs, vbNullString) <> vbNullString Then
Me.cboMeters.RowSource = "Select MeterID, ContractID, MeterRef" _
& " From tblMeters Where ContractID=" & Me.OpenArgs _
& " Order By MeterRef;"
End If
End Sub
You'll need to adjust the above example to match your actual object names, and depending on the data type of ContractID, you may need to add delimiters.
My ContractID is a number field. What do you mean delimiters?
Tried that code and edited it to suit my table that stores MeterRefs. But no joy.
When using the form, it appears the ContractID is not passed to the Prices form/table until you enter the first bit of data, so would this be why if using Open Form?
I have the ContractID field on my Prices form, and it doesn't populate until I enter some data.
What do you mean by "no joy"? You get no records? You get all records? You get an error? We need specifics. What does the code look like now and where did you put it?
When using the form, it appears the ContractID is not passed to the Prices form/table until you enter the first bit of data, so would this be why if using Open Form?
I have the ContractID field on my Prices form, and it doesn't populate until I enter some data.
That's because you're writing the value in the Before Insert event, which doesn't fire until the user enters the first character in a new record. However, that would not prevent you from being able to use the OpenArgs value to set up your combo box. If you have a sanitized copy of your app that you can post, that might be helpful so we can duplicate the problem.
If your ContractID field is a number, then you shouldn't need the delimiters. Just to clarify what I mean, if the ContractID was text, then this line;
Code:
& " From tblMeters Where ContractID=" & Me.OpenArgs
would need string delimiters added like the following;
Code:
& " From tblMeters Where ContractID=""" & Me.OpenArgs & """"
similarly, if you were passing a date value you would need date delimiters (#).
What do you mean by "no joy"? You get no records? You get all records? You get an error? We need specifics. What does the code look like now and where did you put it? - I get no records. I will copy code tonight when I am home. I put the code in the Prices form the one with the MeterRef combo on it.
That's because you're writing the value in the Before Insert event, which doesn't fire until the user enters the first character in a new record. However, that would not prevent you from being able to use the OpenArgs value to set up your combo box. If you have a sanitized copy of your app that you can post, that might be helpful so we can duplicate the problem. - I will attach it tonight
Try this. In the Open event of your Prices form put the following code (again, adjust for correct object names). Comment out any existing code;
Code:
Dim strSQL AS String
strSQL = "Select * From tblMeters Where tblMeters.ContractID=" & Me.OpenArgs
MsgBox strSQL
Then open your Prices form from the command button on your Contracts form (so the ContractID gets passed via OpenArgs). Post back with what the message box says when the Prices form opens.
Try this. In the Open event of your Prices form put the following code (again, adjust for correct object names). Comment out any existing code;
Code:
Dim strSQL AS String
strSQL = "Select * From tblMeters Where tblMeters.ContractID=" & Me.OpenArgs
MsgBox strSQL
Then open your Prices form from the command button on your Contracts form (so the ContractID gets passed via OpenArgs). Post back with what the message box says when the Prices form opens.
The problem is that you've got the columns in your combo box out of whack. You have 13 columns defined in the combo box, with all widths set to 0" except for the 9th column. I'm a bit confused as to why it would have been set up this way because the Meter_Refs table only has 7 columns total. Plus if you use a SQL statement to set the Row Source then you may likely end up with even fewer columns. So the data is there but you can't see it because it's in a column for which you have the width set to 0".
The problem is that you've got the columns in your combo box out of whack. You have 13 columns defined in the combo box, with all widths set to 0" except for the 9th column. I'm a bit confused as to why it would have been set up this way because the Meter_Refs table only has 7 columns total. Plus if you use a SQL statement to set the Row Source then you may likely end up with even fewer columns. So the data is there but you can't see it because it's in a column for which you have the width set to 0".
The problem is that you've got the columns in your combo box out of whack. You have 13 columns defined in the combo box, with all widths set to 0" except for the 9th column. I'm a bit confused as to why it would have been set up this way because the Meter_Refs table only has 7 columns total. Plus if you use a SQL statement to set the Row Source then you may likely end up with even fewer columns. So the data is there but you can't see it because it's in a column for which you have the width set to 0".
Also the 9th column of the combo is the meter ref column of the query.
I used a query as I need to pull fields in from different tables in order to get the data pulled through that the clinet wanted in the afterupdate event.
The STORE_Meter_Refs table stores Meter Ref info from a subform on the Contract tab. So the join between MeterRef and Contract is in there also.
