Selecting multiple records to Ship? Pick Your Brain (1 Viewer)

[hbrehmer]

Hi All,

I am writing an ERP system for our small foundry. I have quite a complex program building, from processing a purchase order all the way through production. I am now at the stage of preparing to ship an order. Some customer orders have multiple part numbers, or we want to ship multiple parts on one packing list. What do you think would be the best way to approach this? I have a query built and a form built on that query where a user can select a record to ship, but I might want them to select multiple records, then open a second screen where they can view the original order details, PO number, etc and enter shipping details, such as # of boxes, weights, pallets, etc. to print a Packing Slip and close an order. Does anyone have an example of that or have any suggestions on how I should proceed?

Thanks for the input!

Heidi

 

[CJ_London]

take a look at this link which is one way to go https://www.access-programmers.co.uk/forums/threads/multi-select-form.289116/

 

[hbrehmer]

I love that feature! Yes, I can use that. How would I get those selected records to populate into another form? Do you have any ideas on the coding?

 

[CJ_London]

a while since I've looked at it, but a hidden textbox called selectedlist gets populated with the PK's of the selected records separated by pipes e.g.

1|33|27|25

when you open the other form put something like the following in the Where parameter of docmd.openform which changes the pipes to a comma and then

"ID IN (" & replace(me.selectedlist,"|",",") & ")"

which should give you

"ID IN (1,33,27,25)"

can't remember if there opening and closing pipes but if there are, you'll need to remove them using

replace(mid(me.selectedlist,2,len(selectedlist)-3),"|",",")

 

[hbrehmer]

That sounds good. Can this work with a query? My data on the form is from a query. I can't seem to make the selected line change color like yours. I built the unbound field and put it behind the other fields and set the conditional Formatting like yours. I can't figure it out.

 

[CJ_London]

without seeing what you are doing, difficult to advise. if you are using a text field as an identifier, you would need to enclose with single quotes for the filter to work

'A'|'B'|'C'

 

[jdraw]

Do you have a data model or prototype of your overall system? You seem to be discussing things at very different levels of detail. Do you mean you are working on the Shipping portion of this ERP system with some concerns of Packing slips or is that the next priority section of your project. Building small prototypes, with minimal functionality, can be very helpful to get feedback from focus groups and refine any analysis and design options.

 

[zeroaccess]

Parts should be sourced from a parts table.

They should be added to a subform as part of a one-to-many (one order, many parts) relationship. You will want to create reports that summarize the order into an invoice, shipping manifest, etc. See attached example.

 

[hbrehmer]

without seeing what you are doing, difficult to advise. if you are using a text field as an identifier, you would need to enclose with single quotes for the filter to work

'A'|'B'|'C'

I figured it out! Thanks.

 

[hbrehmer]

a while since I've looked at it, but a hidden textbox called selectedlist gets populated with the PK's of the selected records separated by pipes e.g.

1|33|27|25

when you open the other form put something like the following in the Where parameter of docmd.openform which changes the pipes to a comma and then

"ID IN (" & replace(me.selectedlist,"|",",") & ")"

which should give you

"ID IN (1,33,27,25)"

can't remember if there opening and closing pipes but if there are, you'll need to remove them using

replace(mid(me.selectedlist,2,len(selectedlist)-3),"|",",")

CJ,
How would I get the selected records from this filter to be used on another form? I'm stumped on how to write that code? Can you offer some assistance here?

 

[CJ_London]

when you open the form put the filter in the where parameter of the openform command

the filter will look something like I described

"ID IN (" & replace(me.selectedlist,"|",",") & ")"

if the form is already open then you would use vba along these likes

forms!otherformname.filter="ID IN (" & replace(me.selectedlist,"|",",") & ")"
forms!otherformname.filteron=true

 

[hbrehmer]

when you open the form put the filter in the where parameter of the openform command

the filter will look something like I described

"ID IN (" & replace(me.selectedlist,"|",",") & ")"

if the form is already open then you would use vba along these likes

forms!otherformname.filter="ID IN (" & replace(me.selectedlist,"|",",") & ")"
forms!otherformname.filteron=true

Hi CJ,
I need your help again. I love how this pipe selection works. Can I use the records selected in the pipe to perform an Update Query? I'd like to have my user select records, then "apply" those records through a button click that runs an update query, so I can set another button to print a report using the new (updated) records. Does that make sense? How would I write that code?

Thank you!

Heidi

 

[CJ_London]

suggest vba code might look something like this

dim sqlstr as string
sqlstr="UPDATE myTable SET myfield=True WHERE ID IN (" & replace(me.selectedlist,"|",",") & ")"
currentdb.execute sqlstr
docmd.OpenReport "",acViewPreview,,"ID IN (" & replace(me.selectedlist,"|",",") & ")"

you may have more to do here

SET myfield=True

depending on whether you are updating one or more fields and field type etc

 

[hbrehmer]

suggest vba code might look something like this

dim sqlstr as string
sqlstr="UPDATE myTable SET myfield=True WHERE ID IN (" & replace(me.selectedlist,"|",",") & ")"
currentdb.execute sqlstr
docmd.OpenReport "",acViewPreview,,"ID IN (" & replace(me.selectedlist,"|",",") & ")"

you may have more to do here

SET myfield=True

depending on whether you are updating one or more fields and field type etc

I would be updating one to many records, depending on how many records are selected. I'm thinking that upon the click event, I could update the corresponding record in the table at that time? The only issue, is that the VSHIPID is from the main form, while the selection of records are from the subform. Should that matter?

Such as:
Private Sub Schedule_Click()

If InStr("|" & VSelectedList, "|" & TKTPID & "|") Then
VSelectedList = Replace(VSelectedList, TKTPID & "|", "")
Else
VSelectedList = VSelectedList & TKTPID & "|"
End If

Dim sqlStr as String
sqlStr="UPDATE TKTProcess SET VSHIPID WHERE TKTPID in (" & replace(me.selectedlist,"|",",") & ")"
currentdb.execute sqlStr

End Sub

 

[CJ_London]

Should that matter?

providing you have the right table, I wouldn't think so

this bit of code is not setting anything and will generate an error

UPDATE TKTProcess SET VSHIPID WHERE

SET VSHIPID to what?

 

[hbrehmer]

Because the data is from the main form, would I SET VSHIPID = [VenShipEntry].Form![VSHIPID] ... then complete with the WHERE statement?

 

[MajP]

You my find this interesting. See post 5.

The Most Powerful form Filter and Requires almost No Code

NOTE: See updated code below. I recommend using the updated class module that @arnelgp modified. It further simplifies building a search form and addresses some bugs...

www.access-programmers.co.uk

 

[Cronk]

Going back to the OP, the requirement is to fill in details for an order. I would say the most relevant post for this is #8, which shows a sample order/order details from Northwind.