VB Question

[spacepro]

Cosmos,

Thanks for that. I wasn't aware about that. Cheers

I have posted a sample db to show you the code and it's generated output in a form.

I have been working on it over xmas but cannot get it right, so I wass wondering if you could help me or point me into the right direction.

Basically I don't want to select part of a product but just round numbers. Anyway have a look at the db becuase there is an explanation on the form that shows my predicament.

Thanks

Andy
(Don't normally ask for this much help as I like to help others as well, but I am told this project is quite difficult and hopefully with what I know and what other people know between us we can piece together the solution.)

Thanks

 

[WayneRyan]

Hi Andy,

I see we're still fighting this thing ... To me it seems like
the rules are changing a bit.

If you can help with a few questions, it might help.

The following is a sample of loading truck #1.

Product      Qty   Rows   Total Lanes  Total Units
=========    ===   =====  ===========  ===========
WMA1510S      30    3.75      3.75         30
WMB10W        30    3.75      7.50         30
D3422FW       42    7.00     14.50         42
DE3861 FW    100    6.50     21.00         39 <-- ?

At this point, we've filled the truck, but how
did you calculate that 39 units filled it? The
ProductCube is 6. How does that equate to 39
out of 100 units?

Where does the Rows information come from? I see
that Rows * ProductCube = TotalUnits. That means
that rows must change ... How?

Also, I see that that product has two entries for
truck #3. Shouldn't it be the first product on
the next truck?

Truck #2 should start like:

Product      Qty   Rows   Total Lanes  Total Units
=========    ===   =====  ===========  ===========
DE3861 FW    61      ?       ?             61
.
.
.

Rows = TotalUnits/ProductCube = 61/6 = 10.1

I can only calculate that because 39 of them filled
6.5 rows.

The last thing is that the table structure is confusing
me. You are storing rows for the product. This is
calculated somehow using the ProductCube. I don't see
how having a QTY and ProductCube determines how many
rows required.

Lastly, relating to the data storage, the information
that should be provided is:

tblShipment:
ShipmentNumber
ProductName
QuantityToShip
ProductCube

If the shipping logic must split products, then a new
table should be brought into play:

tblShipmentTrucks:
ShipmentNumber
TruckNumber
ProductName
QtyOnTruck

That way (even after the fact), you can re-create how
the shipment was sent.

I initially thought that one didn't split Products,
our "filler" record was just empty space, but I guess
that isn't the case in the real app.

Let me know,

Wayne

 

[spacepro]

Hi Wayne,

Firstly the rows calculation is generated by a query that is run which updates the table tblLDCNewark.
The calculation is Qty / ProductCube.

ProductCube = Amount of each product that will fit into a row on the vehicle.(Does not change)

Qty = Order Qty

The table structure may not be ideal and also there might be fields that I will not be using as I am playing about with the design to achieve the end result.
I will be looking at this.

The 39 units is a result of Rows * Productcube. The rows is changed(through code that cosmos supplied) when the max of 21 when looping through the records and then creates a new record with the balance of the original figure. So 6.5 X 6 = 39.

My problem is that you will get 6 products in a row and 8 in others. When The code is run it will select part of a whole product to maximise the vehicle. I have looked at rounding the numbers up to the next whole number but I need to record the exact amount of each product on each vehicle or a calculation which can be generated in a query/report.

I have attached a db with the code:

The following buttons in the db do the following:

View LDC'S = Open the form which shows the data.
Generate Loads = Opens a Form with a progress bar which runs the code on form Load.

I hope I am making this a little bit clearly, and apologise if the rules seem to have changed. It is quite a difficult thing I am trying to achieve and trying to explain it is difficult and confusing sometimes.

Andy

 

[spacepro]

Just thinking, is it possible to add to the code something like:

Cube = rst.fields("Rows") * rst.fields("ProductCube")

If Cube = Between .00 and .99 Then
Round Down And
Goto NextRecord asign value of TruckNum + 1
Else 
goto AssignTruck:
End If

This is not proper code just an idea of how the code should be in access terminology.

Andy

 

[WayneRyan]

Andy,

Yes, it is possible to do that.

I made a new shipping table, and am reworking the code.
I've got the "rounding" stuff down, but have a bug or two
calculating the Start/End lanes.

Should post something soon.

Wayne

 

[WayneRyan]

Andy,

Ok, I have to go do a few things now. This is where I'm at
so far. I just wrote it from scratch, so its a little rough.

Run your load status form, then look at the table with
"Shipment" at the end. It might be OK now, but I don't have
time to check now.

I put the shipping into a new table so that you will have
the Purchase Order documented seperately from the
shipping info.

I'll check back later.

Wayne

 

[spacepro]

Thanks for your help Wayne, speak to you soon.

 

[spacepro]

Hi Wayne,

Had a quick look at it, and I can see what you have done, the only problem is that it allocated over 21 rows on the last record when I entered some new qty's in tblLDCNewark.

And that it looks at the purchase order number, the only issue with this is that there are 3 different purchase orders but all need assigning vehicle numbers.

Explanation:

I import the data from 3 different spreadsheets(different orders)
then I delete blank record/record with 0 etc then I transfer the data to a table using make table query, sort the records then transfer them to tblLDCNewark but only for that site. Then I want to assign vehicle number as per post.

I receive these spreadsheets once a week.
At the moment of which I might change, is that there will be about 18 tables for each Site(18 Sites) doesn't expand or reduce,
the spreadsheet data transferred into Temp Table and creates a Table transferring data then sorts the data and transfers to each Site(table) then the vehicle is assigned, then a query is run to transfer the completed data in a table (tblHistory), then all of the other tables are then emptied using a delete query.

Maybe not the best way to do it but if I can solve my problem then I can then redesign the structure. (Not my normal approach,)

In Test Stage at the moment. I will have a look at the code and see if I can understand and apply it to my situation.

I will speak to you soon when you post back.

Many Many Thanks

Andy

 

[WayneRyan]

Andy,

I'm not surprised that there was an error on the last truck.
I wrote the code in one sitting, so didn't expect it to be perfect.

As for the multiple Purchase Orders. Your main form could
have a combo on it to select a PO. I preserved it in the
code (tho I did delete ALL from the shipment table prior).

I had to "inflate" your quantities so that I had something
to work with.

I'll let you investigate. Let me know how things turn out.

Wayne

 

[spacepro]

???

Wayne,

Thanks for the feedback. I am still looking at your code, and I have noticed there are some glitches in it as far as the vehicle assignment can be wrong and secondly the calculation for the qty I think is wrong.

The qty in shipment table shows 64 but the startlane is 15 and endlane is 21 (total of 6 rows) the cube for the product is 8 so it should be 48.

Anyway I am going to study the code and try to adjust it. The form that you saw is purefully for a visual point of view for testing, in the real app a button will be pressed then the report will be printed showing the breakdown, so I will rethink about the purchase order scenario.
I am also looking at cosmos's db and wondering if I could loop through the records after assignment and if the rows value is between .1 and.5 then round down(Int) and if the value is between .5 and .99 then round up(int +1).

Anyway if you guys have any comments/more ideas(me being cheeky) then please let me know.

The main thing is that you two have given me two ways of addressing this issue, and now I am completely baffled.

The code is very similair in some respects, so I think this will take me quite some time to figure this one out, Thanks a lot for your time.

I will post back as to my success or downfall.

Regards

Andy

 

[spacepro]

Update!

Wayne/Cosmos,

Hi Guys, just to let you know how I am going with my problem.

Wayne,

I have had a look at your code and adjusted it so that it sorted the glitches I uncovered during testing. However I will re-write the code because when I entered quantities of 500+, this should never happen but it could in the future and it through a major wobbly with the calculations. Maybe this was the code I rewrote.

Thanks Wayne.

I have adjusted Cosmos' code to change the qty when looping through the records when checking the records before assigning the vehicle and adding the balance to a new record.

The way the db is designed at the moment is no where ideal, but between us have managed to solve my problem.

Now that I know how to go about it, I will be rewriting the db and adjusted the code ( a combination of both) with regards to table structures etc..

I cannot thank you both enough for all of your time, it as been a pleasure.

Hope you had a excellent Xmas and wish both of you and everyone here at the forum a Happy New Year.

Regards

Andy

 

[spacepro]

Guys,

Sorry to drag this thing on but:

Thinking about the way I was going to structure the Sites(Tables) there would be 18 of them. So what I am thinking if Cosmos code can be adjusted so that it loops through each site(LDC) with the current code. Then I can just have one table.

I assume I would need to set some sort of variable. Bit unsure how to do this.

Please give me a clue.

Many Thanks

Andy

 

[WayneRyan]

Andy,

You should be able to make an unbound combo box that
displays the LDCs available.

The code can reference the combo in a select statement
for the recordsource.

Wayne

 

[spacepro]

Wayne,

Thanks for the speedy reply.

In the app a command button will be pressed and all sites will be assigned vehicles.

Rather than using a combo, the use of forms won't exist as there will be no data entry, they will be only used as a read only function.

Can I loop the code so that it checks all Sites. So say :

Dim Site as string  '???
Site = rst.fields("LDC")

Start Code at First LDC
Goto Code
Then loop through all Sites

Will need a table for all of the sites ?

I am thinking the right way of doing this.?

Thank you

Andy

 

[WayneRyan]

Andy,

Don't know the exact table structure here, but essentially you
can use another recordset. Set up a loop for it surrounding the
current code and all it has for code is a .MoveNext at the bottom
of the current code.

While Not NewRst.EOF and Not NewRst.BOF
  ' Existing code goes here
  NewRst.MoveNext
  Wend

Wayne

 

[spacepro]

Wayne,

The table structure is as follows:

Order  Line No  Product  Qty           Store       
123        1          A            120         London           
123        2          B            100        Birmingham      
123        3          C            230        Cardiff
123        4          A            120         London           
123        5          B            100         Birmingham      
123        6          C            230         Cardiff

What I want to do is to possibly using sql selecting the records and running the code, so for example :

LoopStores:

Dim Strsql As String, strwhere as string
Strsql = "Select Store from tblLDCNewark"
StrWhere = "like Store"

strsql,strwhere
goto runcode:

I have not used sql in vba before and wondering am I on the right track to do what I want.
I want to select all the records for delivery to London and run the code on these records then go to the next store and do the same until the last store.

Or do I create a lookup table with all the stores in and run sql based on the two tables.

Any help appreciated.

Regards

Andy

 

[spacepro]

Another SUgeestion? Good- or Bad

Or Should I rather than using the table, create a query and run the code from the query results?

 

[WayneRyan]

Hi Andy,

You can create a query and run it. Its criteria
could reference some field on your form to determine
the city:

Forms![YourForm]![txtCityName]

Or you can use a recordset:

sql = "Select * " & _
      "From   YourTable " & _
      "Where City = '" & Me.SomeCombo & "'"
Set Somerst = dbs.OpenRecordset(sql)

That assumes that you have a combo where you select
a city and then call the code.

Wayne

 

[spacepro]

Thanks Wayne,

I have managed to sort it and I am well happy now that we have managed to resolve it .

Thank you.

I think this will be the end of this now, Thank you so much

Regards

Andy

 

[spacepro]

I Lied!!!

Hi Wayne/Cosmos,

I Lied!! Only a quick one.

Now that I have the code running I am thinking about the following:

I want to allocate the product from one of four sites.

Now the stock file is exported out of SAP and imported into Access.
I want to add to the code this:

AssignStock:
Dim Site1, Site2, Site3, Site 4 As String

Qty = rst.fields("Rows") * rst.fields("productcube")
Site1 = "London"
Site2 = "Birmingham"
Site3 = "Stoke"
Site4 = "Cardiff"

Select Case Site1
With rst
If Qty matches Product And Qty in Stock Table Then
.edit
.fields("Site) = Site1
.update
end with
end If

This is just a rough idea.

The table is as follows:

Product    Site1     Site2     Site3      Site4
A                100       50         54          1000

etc etc.

I would assume I need to do some sort of lookup of the stock table.
Is Dlookup the answer or would you suggest another idea?

ps. Site 4 normally carries the most stock but only want to allocate complete loads from each site if possible or would this be just too confusing.

Many Thanks in advance

Andy