mapping locations (1 Viewer)

[vladthegreat1]

hey everyone, so i have a small business and i am looking to keep track of my inventory and one of the ways to do that is to do inventory counts. In the past its been tedious due to the layout of the parts because they are organized across multiple different bread racks in boxes. I have over 40 shelves. I want to be able to print out a map of each shelf based on a shelf location in the database table. I am not even sure what to start looking at. I am still new to access although i have developed a few small programs for my church and a few small local companies to keep track of orders and such. Nothing to this scale.
What i am thinking is somehow assigning to where if i look up for example shelf 25 it would pull up a report? and that report would generate and map all the appropriate part boxes on the shelf. I will eventually be using a windows tablet to do my inventory counts but i can work that in later i just want to learn the concept first.
The Dataset is as follows:

I want the report/form to look like the shelf at the bottom but obviously there are more than just part one on shelf 025 i just didnt want to clutter it. does this make sense? Is this doable?

 

[vladthegreat1]

Playing around with some code i found online i am thinking of using something like:

Dim frmMaps As Form, ctlRect As Control
Set frmMaps = CreateForm
Set ctlRect = CreateControl(frmMaps.Name, acRectangle, , , , 5000, 0, 590, 600)
DoCmd.Restore
ctlRect.SizeToFit

I was able to make a button to create a form, add a rectangle to a specific location with a specific size. This is kind of how i want although i want the to be based off a table. the more i research and play around it seems this is doable. Hopefully someone can pitch in and help me understand. Thank you.

 

[vladthegreat1]

oh my gosh! I think i just solved it

Dim frmMaps As Form, ctlRect As Control
Set frmMaps = CreateForm
Set ctlRect = CreateControl(frmMaps.Name, acRectangle, acDetail, , , 100, 100, 12000, 3400)
DoCmd.Restore

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Table1")

If Not (rs.EOF And rs.BOF) Then
rs.MoveFirst
Do Until rs.EOF = True
Set ctlRect = CreateControl(frmMaps.Name, acRectangle, , , , rs!xcor, rs!ycor, rs!bwid, rs!bhei)
rs.MoveNext
Loop
Else
MsgBox "There are no records in the recordset."
End If

rs.Close
Set rs = Nothing


This does exactly what i need it to do i think...
the only thing i dont know about is the coordinates. It seems to be clumping them together like so, I guess I have to play around with them a little.

 

[pbaldy]

You might find ChrisO's drag and drop sample db of interest. I modified it to put pre-created labels on a form according to coordinates stored in a table.

ChrisO Samples

You may run into trouble creating controls on the fly. Forms have a limit of how may controls can be created over their lifetime, even if deleted. I put more than I needed on the form and made them visible as needed. If memory serves, the sample does that.

 

[vladthegreat1]

Thanks Pbaldy! I am going to read this with some tea after the kids are down for bed. I figured out why my boxes are smushed, my data i used "container height" instead of "container length but i am still going to read this because im curious about the control limit. The only hard part about having the controls invisible is there are different container sizes (small, med, large) each one is its own size so one shelf could have 30 small ones and the other could have a mixture of med and large etc.

 

[pbaldy]

No problem. See here near the bottom of the forms section:

Access specifications - Microsoft Support

Find limits and specifications for Access 2016 database files and objects, such as the maximum file size or the maximum number of fields in a table.

support.office.com

I don't see why you couldn't manipulate the size of controls as well as position, though I didn't need to.

 

[vladthegreat1]

Pbaldy, so i tried looking at the code but i guess i am too novice to understand, i dont see a table on the database that you told me to look at and the code is somewhat unclear to me.
I have designed my code right now but its not really efficient, and somewhat slow. I would love to have it do like you said where it puts pre-created labels on a form according to coordinates on a table.
Is there a simplified way of writing that code?

 

[The_Doc_Man]

It is possible to dynamically move controls around. You can diddle with their height, width, top, and left properties. However, you need to consider this: When dealing with the "canvas" on which you are doing the drawing, height and top relate to vertical properties within the current section. On a form you can have a form header, form footer, and detail section easily. On a report, you will also potentially have group headers and footers as well as page headers and footers. So be careful to know the section in which you are working. As to Left and Width, those are always based on the width of the form or report. That is because sections are like horizontal stripes.

Now as to the problem of creating shelves in your drawings, never do this. Instead, you figure out the most number of shelves you can ever have. Make that many rectangles ahead of time (or use text boxes, makes no difference for this). Then you NEVER create anything. You just RESIZE the rectangles and make any unused rectangles non-visible. The trick will be recognizing where to make the highlight, but I have a solution for that, too.

If you make the shelves be numbered according to an Excel pattern (bear with me for a moment here) then if shelf A1 is the top/left, you make it visible. If is then double-high and double wide, you would take shelf A2, B1, and B2, and make them invisible, and make shelf A1's height and width TWO units instead of one. This of course means that on that aisle, nothing can be listed for slots A2, B1, and B2. And you would have A1 in the location field for that aisle. And you could name each of the boxes things like A1, A2, B1, B2... etc. So selecting which box to highlight is simple, you highlight the control with that name using the Me.Controls("name").Visible=True syntax. And Me.Controls("A1").BackColor = vbRed, or something similar to that.

The only problem then is you can never have "half-size" shelves. And setting up these diagrams for each aisle will be a pain in the toches, but it should be a one-time operation unless you have a bad case of redecoration fever among your store managers.

 

[vladthegreat1]

Thanks the Doc Man. I will be trying this tomorrow. I am always hoping layouts would stay the same but somethings the things we produce changes and the parts boxes have to move around with the new changes or new parts get added in. Not a weekly thing but it does happen on occasion. I will let yall know what i get from this Thanks again for your help thus far.