Question Problems with subforms

[axle124]

I have two tables one that has an so number and company name in it with the so being primary. I also have a table that stores everything that goes with that so number and all the information that goes with it. Ie: piece, datescheduled, size(l x w), and dateshipped.

I have a form in which the user can enter a so number and the company whice the order goes with and i have a subform in which you can enter all the pieces that go with that so number. What i am having problems with is when i added a check box to ship the whole shop order, i can only get it to ship the first piece. I also have a txtbox named newshipdate on my mainform.

Dim message, title, default
Dim reposnse As Integer
Dim myvalue As Date
On Error GoTo Err_shipdie_Click
message = "Please enter the date the die shipped"
title = "Shipping dies"
default = date
myvalue = InputBox(message, title, default)
response = MsgBox("Is " & [myvalue] & " the right date shipped", vbYesNo, "Continue")
If response = vbYes Then
newshipdate = myvalue
[so_number_Subform2].Form![dateshipped] = newshipdate
End If
Exit_shipdie_Click:
Me.newshipdate = Null
Exit Sub
Err_shipdie_Click:
MsgBox Err.Description
Resume Exit_shipdie_Click
End Sub

I would appreciate any help i can get because i am stumped.

brandon

 

[Steve R.]

From what I can tell, you will need to create a filter or use an SQL statement to show all the items in the subform that have the same "so number".

FilterNamestr = "[agency] = " & Cnum1
Me!Child31.Form.Filter = FilterNamestr
Me!Child31.Form.FilterOn = True

Of course you will need to adapt the code above for your particular situation.

 

[axle124]

Thanks for the reply, but I already get it to show everything to do with that so number, by just having the childlink and parent link to the so number of each table. That in turn filters to each item under that so. So i dont know how filtering would help. I will try that though, and again thanks for the help.

OK I think i understand, after re reading what you were saying.

 

[axle124]

I am still having problems could anyone help, please

 

[Steve R.]

Please take a look at the two screen shots. I have a main form and subform where the main form defines what the subform displays. In the first form 39 USF&WS records are displayed. That should be equivalent to your "so number", if I understand your question correctly.

In the second screen shot, only 13 records are displayed because the project list is now limited to Dare County. My guess is that would be equivalent to your "shop order".

The sample that I provided generates the screen shot with 13 records. The actual coding because I have six search criteria is quite complex. However, the sample should should be adaptable to your situation. Also the filter is activated with the "On Click" event.

 

[axle124]

Thanks for taking time to help me.


My problem is not getting the so to filter because i do have a filterring section for my page.

The form i have is a data entry form where they can enter a so number and company name and then tab to the subform which is linked to the so number entered on the first tab position, then the user enters the information for that particular order ie what type of object size, charge, and date scheduled. I also have combo boxes that allow the user to find a particular so number to view the information. My problem is that I want to be able to allow the user to click a check box and enter a date that will ship all the items in the subform which is already linked to that so number. I can get it to ship the first record of the subform but that is it. I dont understand why if i make my date shipped of the subform = to the input box on my mainform, it doesnt automatically update every record on that form and not just the first one. And all the records are there and can be seen when the user checks the check box and enters a date, but when the date shipped field is updated it only updates the first record of the so subform which is tied to the so info database. I have a place where the user can ship each piece of the so(shop order), so that We can track when we make a mistake and ship orders that are not complete. I hope this is a little more thorough.

Brandon

 

[axle124]

Does anyone have any ideas??? I can't figure it out at all.

brandon

 

[Steve R.]

I can get it to ship the first record of the subform but that is it. I dont understand why if i make my date shipped of the subform = to the input box on my mainform, it doesnt automatically update every record on that form and not just the first one.

You may have a structural issue with how your database is set-up, or it could relate to requerying the recordset and/or refreshing the form(s).

My problem is that I want to be able to allow the user to click a check box and enter a date that will ship all the items in the subform which is already linked to that so number.

Again, if the "so number" is the controlling field and all your items are linked to it, the sample filter I gave you should give the results you want. However, if the "so number" is ambiguous by itself and requires a reference to the date it may point to a structural problem with your database. For example, you may need a new field called something like "ShippingOrderNumber" that combines the "so number" with the date. (rather than a new field, you could have an SQL statement using "AND")

 

[Scooterbug]

If I could offer a suggestion...

From what I read, you have a SO with details which are displayed in a subform. What you want to happen is have all the items in the details table get updated with a ShipDate. A simple update query should do the trick:

dim stUpdateSql as string
 
stUpdateSql = "UPDATE tblSoDetail SET tblSoDetail.ShipDate= [NewShipDAte]
WHERE (((tblSoDetail.So_NUMBER)=" & me.SoNumber & "));"
 
currentdb.execute stUpdateSql

 

[axle124]

Thanks I will give both a try. I appreciate the help from both of you