Didn't want to have to ask again but....
Have a form with 3 fields then a subform with input fields to input payments. (CID is the unique PK) All from one query
Need to change the 3 fields in the main form to a combo box to facilitate quicker finding of record.
Tried everything I can and everything I can search and find on this.
any suggestions ???
Well, I have found videos and such on the net on making combo boxes and list boxes and watched and read everything I can find.
I still can not make a combo box work for me.
It either won't list the records in a field or when I get it to, it doesn't sinc with other text boxes on the form or won't permit clicking in it to select.
Well Bob....Like almost everything else in MS-Access, there is more than one way to accomplish something and what you are trying to do falls well into that category.
Let me see if I have this right:
As you explained, the Main Form (Parent) is loaded via query. The three TextBox controls on the main form and the SubForm are populated by this query. Right?
If this is the case then the Form was obviously populated based on a previous selection somewhere else, perhaps the Customer Name or the Invoice number. So why would you want to deviate from that? Or is it that you are trying to save a step?
If that's the Case then yes...you need to add a ComboBox to the Main Form so that you can Change from one Customer to another and or perhaps even add a second ComboBox to the Main Form where are you can select the desired record(s) by way of the Invoice number.
In this sort of scenario, my personal choice would be this:
The Main Form is brought into play with nothing in it (No Data - Dead).
The Form contains two ComboBoxes for Customer Name and Invoice Numbers, a TextBox for the Customer Address, and a SubForm for entering Payment information. ALL Controls are unbound.
Before things can come alive, the User must select a Customer Name from a ComboBox which is populated with a query placed into the RowSource property of that very same ComboBox which is ultimately based from the Customers Table. Perhaps something like this:
SELECT DISTINCT CustomerName FROM Customers;
Now, Based on the selection of the Customer Name from within the Customer Name ComboBox, the Address Textbox is automatically filled (via Code) in the Invoice ComboBox comes alive, again, from yet another Select query forced (via code) into the RowSource property of the Invoice ComboBox. Perhaps something like this (in the OnClick Event of the CustomerName ComboBox):
SELECT InvoiceNumber FROM Invoices WHERE CustomerName = '" & Me.CustomerNameCombo & "';"
Now, based on the selection of the Invoice Number ComboBox, the Payments SubForm becomes active with yet another query which is forced into the RecordSource property of the SubForm itself (via code). perhape like this (in the OnClick Event of the InvoiceNumber ComboBox):
Code:
Dim StrgSQL As String
StrgSQL = "SELECT * FROM Invoices WHERE CustomerName = [B]'[/B]" & _
Me.CustomerNameCombo & "[B]'[/B] AND InvoiceNumber = " & _
Me.InvoiceNumberCombo & ";"
Forms("Main Form Name")("Payments SubForm Name").Form.RecordSource = StrgSQL
Now the Main Form is alive.
Select at will...
Hmmmm....Please Backup you DB before playing with this stuff. Thanks.
"Now, Based on the selection of the Customer Name from within the Customer Name ComboBox, the Address Textbox is automatically filled (via Code) in the Invoice ComboBox comes alive, again, from yet another Select query forced (via code) into the RowSource property of the Invoice ComboBox."
It confused me too when I read it again now. The way it should read is:
:========================================================
Before things can come alive, the User must select a Customer Name from a ComboBox which is populated with a query placed into the RowSource property of that very same ComboBox which is ultimately based from the Customers Table. Perhaps something like this:
SELECT DISTINCT CustomerName, CustomerAddress FROM Customers;
By using the above query within the CustomerCombo RowSource property, there will be two columns of Data placed into the CustomerCombo for use. Column(0) is the first column of data and will hold the Customer's Name. Column(1) is the second column of data (which is always related to the item in the first column) and will hold the Customer's Address.
Now, Based on the selection of the Customer Name from within the Customer Name ComboBox, the Address TextBox is automatically filled (via Code), then the Invoice ComboBox comes alive, again, from yet another Select query forced (via code) into the RowSource property of the Invoice ComboBox. Perhaps something like this (in the OnClick Event of the CustomerName ComboBox):
Code:
Private Sub CustomerCombo_Click()
[COLOR="DarkGreen"]'Fill In the Customer Address TextBox.[/COLOR]
Me.AddressTextBox = Me.CustomerCombo.Column(1)
[COLOR="DarkGreen"] 'Fill In the RowSource Property of the Invoice ComboBox.[/COLOR]
SELECT InvoiceNumber FROM Invoices WHERE CustomerName = '" & Me.CustomerNameCombo.Column(0) & "';"
End Sub
I modified your procedure a bit and tried ONE combo with CID, Name, Addr, and Srvamt, which are the 4 fields of data I need to display in the main form.
Sometimes she has checks with the CID sometimes not then has to go by name and if that is duplicated, then address.
However, when I select one from the list, it only displays the CID (Cust ID).
I tried 1 then 0 in the Bound Column property. ?????
Was there a different reasoning you had for using two boxes or am I getting way out in left field here ?
So now you have four columns of data loaded into your ComboBox. Leave the Bound Column at 1. If you want to display these CombBox items within its' drop down list then you need to also set a few more ComboBox properties. Under the Format Tab in the Properties Window set the Column Count property to 4. Then set the Column Widths property like this: 0.5";1";1";0.5". Then set the List Width property to 3".
Now when you open the ComboBox drop Down list you will see each of the four Data Columns. The first column contains the CID data is considered as Column 0. The data in this Column can be referenced and put to use in this fashion:
MsgBox "CID = " & Me.CustomerNameCombo.Column(0)
The second Column displayed within the ComboBox is considered to be Column 1. You see...Columns within the ComboBox always start from 0. Although we can see Columns 1, 2, 3, and 4, what we are really viewing are columns 0, 1, 2, and 3. It's Still 4 columns, we just need to think of Column 1 as Column 0. The data in this Column can be referenced and put to use in this fashion:
MsgBox "Name = " & Me.CustomerNameCombo.Column(1)
and so on. I think you get the picture.
The reason why I indicated two ComboBoxes in my previous posts is simple a matter of preference on my part. The second ComboBox simply allows the user to select a record base on a Invoice number rather than the CID or the Customer's name. Either or type of thing. Besides....to ensure the proper record is displayed, it's a good idea to supply a reference to point you directly to it.
If the Customer happens to 6 Invoices on the go and making specific payments on each of them, you can quickly hop to that particular Invoice. You don't need to use it. Ignore it if you like and any documentation related to it.
You can, if you like, use the the mere data contained within the Customers Name ComboBox to Filter the records into your Form.
