Tab Control Problem 2

[billmack30]

We fix widgets

We get as many as 180 widgets in at a time to fix, in as many as 6 different boxes

I would like to enter these 180 widgets in as a batch, and be able to separate on forms/reports by Batch Number and the box they came in

I am open for suggestions on how to go about entering these units in by getting the BatchID and BoxID correct

Ideally I would like to have a form with the main form having the Batch Information ( BatchID (Autonumber), who they're from , date received and number of boxes) and have the subform be a tab control with the number of tabs being equal to the number of boxes and being able to enter in the widget's serial number under the proper tab

For those who think they can help me here are the specific's

Two tables involved:

Batch table:
BatchID (Autonumber), Who they're from, date, how many boxes there are

Widget table:
Serial, BatchID, Box number,other non important fields

I would like the Batch table as the main part of a form, and as a subform (based off the widget table) I would like to set up a tab control so that when the form loads the number of tabs that appear are the number of boxes in the batch and the tab names being numbered 1 through however many boxes there are, then as you click on the tabs you can enter the serial number and in the widget table will be entered serial, batchid, and the box number (the box number being the tab the serial number was entered under)

How would I go about doing this, I think i have the problem of the number of tabs appearing equal to the number of boxes in the batch solved, it's more of I dont know how to enter in the widgets table the number of the tab as box number

Thanks for any help!

 

[ZOR]

Why cant you just have a listbox filled on the subform, containing all the boxes, and when you click on one item the relevant data is displayed. Be a lot easier, and avoid too many tabs trying to be displayed etc. Maybe someone else might suggest a better direction?

 

[ZmrAbdulla]

How about using a main form (Widget Batch) and two subforms(BoxID,Widget details)?
When you select a Widget Batch then the BoxID subform will reqery with that batch and clicking on any BoxID will requery Widget details subform to that BoxID Group.

See NorthWind DB "Customer Orders" form for a display

Zameer Abdulla
Visit Me (New Look & style)

 

[willir]

Bill

I would like to set up a tab control so that when the form loads the number of tabs that appear are the number of boxes

You can do this by taking advantage of...
- using visible / invisible property of tab pages
- you have the ability to dynamically control the record source of the a subform (or main form for that matter)

You will have to create six different subforms, one for each box. Determine how many boxes exist for the batch. Decide how many subforms / pages to make visible. And then set the RecordSet for each affected subform.


...Inside a loop for the number of boxes for the batch

strSQL = "SELECT * FROM tblWidget WHERE BatchID = " & Me.BatchID & " AND BoxNumber = " & intX

(intX = 1)

Me.YourSubForm1.Form.RecordSet = strSQL
Me.YourSubForm1.Requery


...However, having different tabs for each batch may perhaps make analysis difficult. Understood - you want to display each box. But what if you want to display all widget together, or sort / analyze by widget type, etc.

There are two other ways of handling this...
- A simple combo box or list box that controls which box or all boxes to display in a subform.
- Have two subforms, one serves as a summary list (box number, number of widgets in the box). Click on a selected box, and the contents of the box are displayed in an adjacent subform.

Both these techniques take advantage of resetting the RecordSource for a subform as explained above. And coding and effort invovled would be less.

BTW, with your design, you may want to add a tblBox to track the box.

tblBatch
BatchID

tblBox
BoxID
BatchID
BoxNum

tblWidget
WidgetID (or serial)
BatchID
BoxID

Richard


Richard

 

[billmack30]

Thanks for the tips, I'll try them today

 

[billmack30]

OK, I went with the Combo Box, wrote the loop to fill it with the correct number of boxes when the form loads now how do I make sure the subform only displays or enters in the correct box number, aka the current value of the combo box

willir- i used your strSQL statement but got run time 3251
operation is not supported for this type of object

Private Sub cmbBoxNumber_AfterUpdate()

Dim strSQL As String


strSQL = "SELECT * FROM tblWirelessUnits WHERE BatchRecID = " & Me.BatchRecID & " AND UnitRecBoxNum = Me.cmbBoxNumber.Value"

<ERROR HERE>Me.tblWirelessUnits_subform.Form.Recordset = strSQL
Me.tblWirelessUnits_subform.Requery

End Sub

 

[ZmrAbdulla]

Me.tblWirelessUnits_subform.Form.[b][COLOR=red]RecordSource [/color][/b]= strSQL

Zameer Abdulla
Visit Me (New Look & style)

 

[billmack30]

Thanks Zmr, its those little things that kill me. now for the thing i have no idea how to do. getting the correct box number to insert into the widget(wirelessunits) table. I select the box number from the combo box and begin to enter units but obviously the box number is blank in the table. i would imagine it would be some sort of insert into table statement, i'm guessing the current combobox.value?

 

[ZmrAbdulla]

Private Sub ListBox1_AfterUpdate()
    Me.TextBox1.Value = Me.ListBox1.Column(0)
End Sub

Zameer Abdulla
Visit Me (New Look & style)

 

[ZmrAbdulla]

Sorry posted a ListBox sample, infact it is same to the Combo also.

Private Sub ComboBox1_AfterUpdate()
    Me.TextBox1.Value = Me.ComboBox1.Column(0)
End Sub

Zameer Abdulla
Visit Me (New Look & style)

 

[billmack30]

I went a different route and added a box table so now i have

tblBatch (BatchID)
tblBox (BoxID,BoxNumber,BatchID)
tblWIdget (BoxID)

and made a form

Main-TblBatch
SubForm-tblBox
SubSubform-tblWidget

and got it working fine, long story short i wanted to make a combo box where it lists the box numbers from tblBox and when entering batches you can add boxes to batch by entering in combo box; wrote a not in list statement to put that box in the tblBox

Private Sub cmbBoxes_NotInList(NewData As String, Response As Integer)

Dim cnn As New ADODB.Connection
Dim strSQL As String

Set cnn = CurrentProject.Connection
strSQL = "INSERT INTO tblBoxRec(BoxNumber) VALUES('" & NewData & "')"

Response = acDataErrAdded
cnn.Execute strSQL

End Sub

that works but it does not enter in the current batchid so my question is, is there a way to lengthen the Insert Into statement to say also in tblBoxRec(BatchID) insert the current Batch ID?

 

[ZmrAbdulla]

I think all these hassle can be eliminated if you can create a Main form(frmBatch),a subform(frmBox)and a subform(frmWidget) inside frmBox.
frmBatch > frmBox (Relation: frmBatch/Master=BatchID frmBox/Child=BatchID)
frmBox > frmWidget (Relation: frmBox/Master=BoxID, frmWidget/Child = BoxID)

Zameer Abdulla
Visit Me (New Look & style)

 

[billmack30]

zameer- that's exactly what i did and it works great, i just wanted to tweak the way boxes were to be entered

 

[ZmrAbdulla]

Zameer Abdulla
Visit Me (New Look & style)