Solved Display the list of selection in different textboxes instead of combobox

[bnefit]

Hi, I am wondering if this could be done.

I have created a tblEquip, qryEquip and frmEquip. When cboLoc is selected, all the items belong to that Location will be populated in cboItem.

However, I would like to make the number of textboxes visible depending on the number of items in cboItem and display them in the textboxes.

For example:
After cboLoc is selected, if there are 4 items in cboItem, 4 textboxes will be visible and the 4 items will be displayed in each of the 4 textboxes.

Is this achievable and if so, is there any examples for me to follow so that I can try to write the code?

 

[arnelgp]

use a subform instead of textboxes.

 

[moke123]

what about a listbox?

 

[bnefit]

what about a listbox?

I thought about using a listbox but I'm not sure if it suits my needs. Once the items are displayed in individual textboxes, I want to select Yes/No for the inspection as shown in the photo below. Hence, I thought it will be neater to do it in textbox.

Anyway, I have written the following code which works for displaying the items on the textboxes. I have also written the code to display the number of items to be displayed, but I have not figured out how to make use of the Count to make the number of textboxes visible....

Private Sub cboLoc_AfterUpdate()

Me.cboItem.Requery
Me.cboInspect1 = Null
Me.cboInspect2 = Null
Me.cboInspect3 = Null
Me.cboInspect4 = Null
Me.cboInspect5 = Null
Me.cboInspect6 = Null
Me.cboInspect7 = Null
Me.cboInspect8 = Null
Me.cboInspect9 = Null
Me.cboInspect10 = Null

Me.txtCount = DCount("[Item]", "tblEquip", "Location='" & Me.cboLoc & "'")

Me.txtItem1 = Me.cboItem.Column(0, 0)
Me.txtItem2 = Me.cboItem.Column(0, 1)
Me.txtItem3 = Me.cboItem.Column(0, 2)
Me.txtItem4 = Me.cboItem.Column(0, 3)
Me.txtItem5 = Me.cboItem.Column(0, 4)
Me.txtItem6 = Me.cboItem.Column(0, 5)
Me.txtItem7 = Me.cboItem.Column(0, 6)
Me.txtItem8 = Me.cboItem.Column(0, 7)
Me.txtItem9 = Me.cboItem.Column(0, 8)
Me.txtItem10 = Me.cboItem.Column(0, 9)
End Sub

 

[moke123]

That looks like you need a subform. No code involved.

 

[CJ_London]

I agree - this should be a continuous subform

I have not figured out how to make use of the Count to make the number of textboxes visible....

perhaps something like this - you'll need to add your own error handling

dim i as integer

for i=1 to Me.cboItem.recordset.recordcount
    Me("txtItem" & i)=Me.cboItem.Column(0, i-1)
    Me("txtItem" & i).visible=true
next i

for i=Me.cboItem.recordset.recordcount+1 to 10
    Me("txtItem" & i).visible=false
next i

now you will need lots of code to update the right record with each of your inspected values - which unfortunately because you don't have have the necessary information - i.e. the PK, you will need to figure out

A continuous subform (or form) requires no code at all - at least in terms of what you are trying to do

 

[bnefit]

Taking the advice of all, I have now use a continuous subform (I hope it is a continuous subform). When cboLoc is changed, Item and Test Dates will be updated accordingly.

Is it possible to disable the Test Date for a particular item so that the user is unable to modify/change the date? I would like to that by adding a Yes/No combobox and if "No" is selected for the 2nd item, the test date will be disabled.

 

[arnelgp]

on design view of your form, click on test date.
on its Property Sheet->Data, set Locked to Yes.

 

[CJ_London]

you would use conditional formatting for the testdate control - something like

expression is....not [mycombo]

then set the format to disabled

this assumes myCombo has a boolean controlsource to a field in your table

 

[bnefit]

Made some progress but it seems like I have more questions than before

1) I have "Inspected" field in tblEquip and the format is "Yes/No". I added the "Inspected" field in the subform via "Add Existing Fields". However, I can only insert it either as Check Box, Toggle Button or Option Button. In this instance, I have selected the Toggle Button option. I have also used conditional formatting for the Test Date control as suggested by @CJ_London and it works.

Nonetheless, I prefer it to be Combo Box. Can I do that?

I have also tried creating a 2nd table tblNotes with "Inspected" field as "Yes/No. I then inserted a Combo Box into the subform and the Row Source as SELECT DISTINCT [tblNotes].Inspected FROM tblNotes ORDER BY [tblNotes].Inspected;
However, when I made selection "Yes" to the first line item, the rest of the items automatically gets updated with "Yes". I want to be able to select the status for individual item.

2) Any changes/updates to the records gets updated onto tblEquip automatically, i.e. "Inspected" and "Test Date". Is it possible to deactivate the automatic updating? I would like to add a Command Button "Save" and the records will only be updated after clicking "Save".

3) After selecting the Location (cboLoc), the records in the subform are refreshed. Currently, "Inspected" and "Test Date" will display the current information of the record as per tblEquip. Is it possible to display "Inspected" and "Test Date" as blank so that the user is not fed with the existing information?

 

[arnelgp]

1. tblNote must have a FK field to be link to tblInspection.
2. you need Transaction to not automatically save your changes.
3. use the Conditional format you learned from cj london.

 

[bnefit]

I have also tried creating a 2nd table tblNotes with "Inspected" field as "Yes/No. I then inserted a Combo Box into the subform and the Row Source as SELECT DISTINCT [tblNotes].Inspected FROM tblNotes ORDER BY [tblNotes].Inspected;
However, when I made selection "Yes" to the first line item, the rest of the items automatically gets updated with "Yes". I want to be able to select the status for individual item.

I finally figured how to get the combo box to work. What was missing was the Control Source. I just need to add the field "Inspected" into the Control Source.

 

[bnefit]

2. you need Transaction to not automatically save your changes.
3. use the Conditional format you learned from cj london.

2. This is a new terminology to me....I need to look up some examples.....

3. Ok..I will try. cboLoc is located in the Form Header while the subform is in the Details. If I want to use cboLoc in the conditional formatting, will I need to express it as ([Forms!frmEquip!cboLoc]) or just simply ([cboLoc]) will do?

 

[arnelgp]

use ([Forms!frmEquip!cboLoc]

 

[bnefit]

Hi all. thanks for your help.

With my limited knowledge, I was unable to progress with subform as per all your suggestions to achieve the desired outcome. Hence, I have reverted back to writing lots of If..else statements in frmEquip to achieve what I want.

It may be arduous but at least I got it going in the end....