Show object only if data available

[1eye1vision]

Hi all it's been a while since i was on here, and also a while since i was playing with access. However i have a problem which i need some help with.
Firstly i have a form which has 2 list boxes on it. On the listbox1 you can double click an output format which then appears on listbox2, what i am trying to do is once an output format is selected, once it appears on listbox2 i want a quantity box to appear next to it.!! however do not know how to code this problem.


The layout is as follows.
1. Listbox1 (Output Available) unbound to tbl (tlkpPrefSize) 2 columns (PrefSize, Default which is a yes/no value)
2. Listbox2 (Output Selected) unbound to tbl (OutputSelected) 2 columns (IDTask, IDOutput) both are primary values.
At present i have a Qty box which i have to manually place the Qty in, but they are always visible which i dont want, i only want it to appear if there is data available.
I have attached a screen grab of the part of the form i am talking about, any help would be appreciated.

[June7]

I don't understand. Why unbound controls? Why the intermediate listbox? Why multiple quantity boxes? Can you make project available? Attach to post, zip if large. Make copy, remove confidential data, run Compact & Repair.

Set quantity boxes Visible property to No. Use VBA code in some event (AfterUpdate of first combobox) to toggle this property.

[1eye1vision]

Hi june7, i thought this was explained well enough, the end user has to select different output formats, this is then shown in the output selected listbox.
Now for every output format the user may require different amounts. That is the reason for the multiple qty boxes. the ones in the picture are for demonstration purposes only.
Basically what i want is that depending on how many types of output are selected then the qty box for each one will appear next to it.

The reason for the unbound listbox is that behind them is a query.

The following code is used:

QryOutputAvailable (listbox1)
SELECT tlkpSizeT.PrefSize
FROM tlkpSizeT LEFT JOIN qryOutputSelected ON tlkpSizeT.PrefSize = qryOutputSelected.PrefSize
WHERE (((qryOutputSelected.PrefSize) Is Null));
QryOutputSelected (listbox2)
SELECT tlkpSizeT.PrefSize
FROM tlkpSizeT INNER JOIN tblOutputFormatT ON tlkpSizeT.PrefSize = tblOutputFormatT.IDOutput
WHERE (((tblOutputFormatT.IDTask)=[forms]![frmTask]![TaskNum]));

To Move Output Format from one box to other
Dim db As Database
Dim rs As DAO.Recordset
Dim varItem As Variant
Dim lngItem As Long
Dim strItem As String
Dim rsMan As DAO.Recordset
Dim strsql As String

Set db = CurrentDb
Set rs = db.OpenRecordset("tblOutputFormatT", dbOpenDynaset)

'Add Record to tblOutputFormatT
With Me!lstAvailable
For Each varItem In .ItemsSelected
' lngItem = .Column(0, varItem)
strItem = .Column(0, varItem)

With rs
.AddNew
!IDTask = Me.TaskNum
!IDOutput = strItem
.Update
End With

Next varItem
End With

rs.Close
Set rs = Nothing
Set db = Nothing

'Unselect item in list
' With Me!lstAvailable
' For lngItem = 0 To .ListCount - 1
' .Selected(strItem) = False
' Next lngItem
' End With

'Requery the lists to update
Me.lstAvailable.Requery
Me.lstSelected.Requery
Me.lstAvailable = vbNullString


However although these move to a table i cannot figure out how to only show quantity if there is data on the table, and only for the selected output formats.

I have attached a screen grab of the table with data on it.

[June7]

Okay, first listbox is a multi-select and second is to display selected items saved to table. You want to show quantity textboxes only for the selected items. I still don't understand what the quantity is all about, where entered quantities get saved. However, controlling display of controls should not be difficult. I have never made use of a multi-select combobox so some guessing here. Perhaps in the above For Each VarItem loop can include If Then or Select Case structure to set the comboboxes.

[1eye1vision]

June7,
Just to clarify, the output formats are print sizes, when a job comes in the customer may require 10 x 10x10" prints and 4 7x5" prints. This form then tells the printer how many of each size are required. Entered quantities at the moment get saved on another table, which are called through another qry.

[June7]

Okay, starts to make sense, must be photo processing.

So have you attempted coding as I suggested? Use the value of the variable strItem as the parameter for setting visibility of textbox.

[1eye1vision]

So have you attempted coding as I suggested? Use the value of the variable strItem as the parameter for setting visibility of textbox.

Ah you see this is where the problem starts, i'm not sure how to start coding this, using VarItemn loop.
Any ideas or samples???

[June7]

Maybe don't need strItem after all.

Is each quantity textbox associated with a specific size value? Or do you just want the same number of textboxes to be available as there are items in the second listbox? Are textboxes named similarly, like tbxQty1, tbxQty2, etc?

[1eye1vision]

Maybe don't need strItem after all.

Is each quantity textbox associated with a specific size value? Or do you just want the same number of textboxes to be available as there are items in the second listbox? Are textboxes named similarly, like tbxQty1, tbxQty2, etc?

No Each qty txtbox is not associated with a specific size value, But yes i just want the same number of txt boxes to be availiable as there are items in the second listbox.
And yes the txtboxes are names similarly , called NumOutput, Numoutput1 so forth up to 5 as there are siz boxes available.

[June7]

Okay, try (and not within the For Each varItem loop):

For i = 1 To Me.lstSelected.ListCount
Forms(Me.Name).Controls("NumOutput" & i).Visible = True
Next

The boxes will all need a number suffix (1-6).

[1eye1vision]

June7,
When you say do not put this inside the Varitem loop, how about the onload event of the form,

[1eye1vision]

Ok say i placed the code as suggested outside the VarItem loop, and on the Onload event of the form, unfortunately it made no difference to the Qty boxes, however when i changed the visible to False instead of True, some of the boxes were missing. See attached Image

Also, if for instance the first record has no outputs, all the boxes show, but when you move through the records there is no change to the boxes.

[June7]

My suggested code has to be in the same event as the varItem loop, say after the Requery lines.

You said these listboxes were unbound. So how do they get populated when the form loads or moving between existing records?

Use the OnCurrent event to set properties when moving between existing records on a form.

That's the trickiness in coding, figuring out everywhere code needs to be triggered to get desired behavior. Might put the code to set the quantity boxes in a general sub behind the form that can be called by any event of the form or its controls.

Do you want to make project available? Attach to post. Run Compact & Repair and zip if large.

[1eye1vision]

Sent you a Private Message!!

[1eye1vision]

Ok so i've put the code in and as stated before it sort of works, but as you can see from the pictures, the first box only dissappears after the second selection of the listbox.
However when you scroll through the records the other records have the same qty boxes missing.
Also when the output is deselected the qty box does not go.