Dustin Stanley
asked on
MS Access Product Inventory Movement Transfer Form With Separate Records Combined Into one Form
Ok I am going to explain this the best I can. I am trying to create a Form for our inventory transfers. There will always be a exact place where the product comes from and where the product is going.
This form will be used only for Inventory transfers. Such as Shelf to Shelf, Bin to Bin, or Shelf to Bin and etc.....
This is my idea so far as to what I need. For the Form
There will be 4 fields in this form
All 4 fields are in the ProdMovements Table and linked to other tables by relationships.
Fields are:
On the form will be a Command Button:
**Event/Actions***
**ReasonMove Table**
I would like for the command button on click Run1 make a New record row in the ProdMovements Table (e.g. ProdmovementID 555) with the ReasonMoveID field in ProdMovements Table Automatically set to 14
Then for the command button on click Run2 make a New record row in the ProdMovements Table (e.g. = ProdmovementID 556 or 777 etc...) with the ReasonMoveID field in ProdMovements Table Automatically set to 13
This form will be used only for Inventory transfers. Such as Shelf to Shelf, Bin to Bin, or Shelf to Bin and etc.....
This is my idea so far as to what I need. For the Form
There will be 4 fields in this form
All 4 fields are in the ProdMovements Table and linked to other tables by relationships.
Fields are:
- ProductID (Found In the Products Table)
- QtyMove
- LocID (Found In the Locations Table) (Caption: From Location)
- LocID (Found In the Locations Table) ****Prodmovements table truly only has 1 LocID field in it.****(Caption: To Location)
On the form will be a Command Button:
- The Command Button is a two Event/Action process (In/Out)
- 1 Event/Action ran at a time. Run1 and then Run2
- Run2 can NOT be started until Run1 Event/Action has already taken place And you do another Event/Action(Example Click)
- e.g. = Click Run1 and then Click again Run2
**Event/Actions***
- (For Inventory Transfer From)
- (For Inventory Transfer To)
**ReasonMove Table**
- ReasonMoveID = 14 = Transfer Out
- ReasonMoveID = 13 = Transfer In
I would like for the command button on click Run1 make a New record row in the ProdMovements Table (e.g. ProdmovementID 555) with the ReasonMoveID field in ProdMovements Table Automatically set to 14
Then for the command button on click Run2 make a New record row in the ProdMovements Table (e.g. = ProdmovementID 556 or 777 etc...) with the ReasonMoveID field in ProdMovements Table Automatically set to 13
ASKER
a form does not have fields; it has controls.
The Control source of a control can be a field
The Record source for a form determines where the data is stored.
Noted!
Will the locations for IN and Out be specified at the same time?
They will be specified at the same time but the records would be separate. In my ReasonMove Table Record Row 14 is Transfer Out and Record Row 13 is Transfer in. These can be used as the Movement Flag 13/14.
The Form should have a 2 Step Process.
Step 1 Transfer Out (Record Created (Flag14))
Step 2 Transfer In (Record Created (Flag13))
Separate Records in ProdMovements Table
ASKER
Would I base the form from a update query?
What Events would be needed in the Command Button to Reduce and Increase the values from the fields in my form to the ProdLocations table? Make inventory counts go up and down and change locations.
Say I was to move 20 different products at once could I lot these together in a ID of there own?
LotMovementID 203
What Events would be needed in the Command Button to Reduce and Increase the values from the fields in my form to the ProdLocations table? Make inventory counts go up and down and change locations.
Say I was to move 20 different products at once could I lot these together in a ID of there own?
LotMovementID 203
it will be much easier if you do not have to test the flag to find out whether to add or subtract. therefore, add this field to ProdMovements:
InOut, integer
If you will store this in 2 records, it would also be better to 2 subforms (or one subform twice) on a mainform so you can identify location from and location to at the same time, when desired
Before update/append queries, the table structure needs to be right. also, you will need a UNIQUE index on the combination of LocID and ProductID in ProdLocations
InOut, integer
If you will store this in 2 records, it would also be better to 2 subforms (or one subform twice) on a mainform so you can identify location from and location to at the same time, when desired
Before update/append queries, the table structure needs to be right. also, you will need a UNIQUE index on the combination of LocID and ProductID in ProdLocations
ASKER
Could you do me a favor please and shoot me some code of what it would look like to test a control. I want to know exactly what to look for. I am very determined to figure this all out. I want to slim this form up and dummy proof it very much. As it will be used alot.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great Thanks!
ASKER
By the way I have to have a continuous form.
then you will want to make it very obvious which record is selected on each side (in/out) for better synchronizing -- we can cover that later. I would suggest you divide the mainform in half vertically to minimize data entry errors.
btw, you can use ONE form multiple times in different subform controls on a mainform -- even change colors and other stuff to make them appear different.
btw, you can use ONE form multiple times in different subform controls on a mainform -- even change colors and other stuff to make them appear different.
ASKER
Ok would it be ok to use Products.ProdLoc for the From(Out) and Locations.LocID for the To(IN) controls?
We would only be taking from the locations the products are obviously in and then transferring them to wherever.
We would only be taking from the locations the products are obviously in and then transferring them to wherever.
if you are going to use different sources for LocID, it would probably be easier for you to understand if you make 2 forms ... but just make ONE for now till it is good. Then you can copy it to modify for the other side.
Since they will probably fill IN and OUT at the same time, it would be best to see both sets of records.
I guess it makes better sense to do OUT first and put that on the left.
Since they will probably fill IN and OUT at the same time, it would be best to see both sets of records.
I guess it makes better sense to do OUT first and put that on the left.
ASKER
ok I will work on it tomorrow thanks.
ASKER
Ok I know this is not exactly what you advised and I have have worked on what you told me to do also. But I wanted to experiment some and see if I could get some thought from you. I created a form. I then duplicated that form one called frmProdMovementsOUT
and another called frmProdMovementsIN. I made frmProdMovementsOUT a subform of frmProdMovementsIN.
I then make both form as small as possible and make all the background and everything White.
I am using field ProdLocID for the Out and field LocID for the IN.
Both forms have all the same 5 fields:
frmProdMovementsOUT I made LocID field Visible NO.
frmProdMovementsIN I made ProductID, ReasonMoveID, QtyID,ProdLocID Visible NO.
I layered the 2 forms together to look like 1 single form.
frmProdMovementsIN I made ProductID, ReasonMoveID, QtyID,ProdLocID have control sources of frmProdMovementsOUT same fields.
Example Code:
I don't know. I thought this would make it look more like a single form instead of two????
Here are some photos of the combining:
and another called frmProdMovementsIN. I made frmProdMovementsOUT a subform of frmProdMovementsIN.
I then make both form as small as possible and make all the background and everything White.
I am using field ProdLocID for the Out and field LocID for the IN.
Both forms have all the same 5 fields:
- ProductID
- ReasonMoveID
- QtyID
- ProdLocID
- LocID
frmProdMovementsOUT I made LocID field Visible NO.
frmProdMovementsIN I made ProductID, ReasonMoveID, QtyID,ProdLocID Visible NO.
I layered the 2 forms together to look like 1 single form.
frmProdMovementsIN I made ProductID, ReasonMoveID, QtyID,ProdLocID have control sources of frmProdMovementsOUT same fields.
Example Code:
=[Forms]![frmqryProdMovementsIN]![frmqryProdMovementsOUT].[Form].[ProductID]
I don't know. I thought this would make it look more like a single form instead of two????
Here are some photos of the combining:
you aren't done with one form yet. Delete the copy -- wait until one form is COMPLETELY done and WORKING before making the second one. For now, I want to see just one screenshot of the DESIGN VIEW of the form
>"frmProdMovementsIN I made ProductID, ReasonMoveID, QtyID,ProdLocID have control sources of frmProdMovementsOUT same fields." -- this is not right anyway
The textboxes need to be changed to comboboxes
1. display the Property Sheet if it is not showing (Alt-Enter or choose "Property Sheet" from the DESIGN ribbon or right-click and choose "Properties")
2. right-click on, for instance, ProductID in the design view
3. choose Change to > Combobox
4. make sure Name is also ProductID
5. on the DATA tab of properties, click in the RowSource property
6. click on the Builder Button ... to the right
7. add the tables you need (Products, SKus, Conditions)
8. on the grid, make the first column the ID field you are storing (ProductID)
9. in the second column, create the text that will be scanned -- combining the SKU and the CondCode was answered in a previous thread
10. Save and Close
11. you will see an SQL statement in the RowSource
12. set ColumnCount = 2 (Format tab of Properties)
13. set ColumnWidths = 0;2 (are you using inches?)
14. set ListWidth = sum of column widths + 0.2" for scrollbar -- so 2.2 (inches)
15. on the Other tab, make sure StatusBarText is set for the user
change all the other ID textboxes to comboboxes and set the properties
put the AutoNumberID and dtmAdd and dtmEdit in the form footer.
>"frmProdMovementsIN I made ProductID, ReasonMoveID, QtyID,ProdLocID have control sources of frmProdMovementsOUT same fields." -- this is not right anyway
The textboxes need to be changed to comboboxes
1. display the Property Sheet if it is not showing (Alt-Enter or choose "Property Sheet" from the DESIGN ribbon or right-click and choose "Properties")
2. right-click on, for instance, ProductID in the design view
3. choose Change to > Combobox
4. make sure Name is also ProductID
5. on the DATA tab of properties, click in the RowSource property
6. click on the Builder Button ... to the right
7. add the tables you need (Products, SKus, Conditions)
8. on the grid, make the first column the ID field you are storing (ProductID)
9. in the second column, create the text that will be scanned -- combining the SKU and the CondCode was answered in a previous thread
10. Save and Close
11. you will see an SQL statement in the RowSource
12. set ColumnCount = 2 (Format tab of Properties)
13. set ColumnWidths = 0;2 (are you using inches?)
14. set ListWidth = sum of column widths + 0.2" for scrollbar -- so 2.2 (inches)
15. on the Other tab, make sure StatusBarText is set for the user
change all the other ID textboxes to comboboxes and set the properties
put the AutoNumberID and dtmAdd and dtmEdit in the form footer.
If controls are in a layout, so that you can size them individually, remove the layout b y clicking in the upper left of the group of controls on the + in a box and from the Arrange ribbon tab, choose Remove layout
since your form is so skinny, only keep dtmEdit and the AutonumberID in the footer. Make the size smaller then best-fit the controls. Put them on the same row.
On the form BeforeUpdate event, to change the date it was edited:
Since the InOut control is hidden, it can be smaller and its label deleted.
I thought you wanted this to be a continuous form -- did you change your mind?
since your form is so skinny, only keep dtmEdit and the AutonumberID in the footer. Make the size smaller then best-fit the controls. Put them on the same row.
On the form BeforeUpdate event, to change the date it was edited:
me.dtmAdd=Now()
Since the InOut control is hidden, it can be smaller and its label deleted.
I thought you wanted this to be a continuous form -- did you change your mind?
ASKER
make font smaller in the footer controls. No bold. They are only there in case someone want to look -- but they shouldn't detract from the important information.
In design view, select form by clicking in upper left where rulers intersect. On property sheet, change Default View to Continuous Forms on the Format tab.
Then:
make the form header section taller, temporarily, by moving mouse to bottom border of that section. When cursor share changes to horizontal line with double-headed vertical arrow, click and drag down.
Select the labels in the detail section, cut them, then click on form header section bar, and choose paste. Move labels to be over the respective controls. Change label captions to be friendly and make the font smaller. Best-fit so the height gets smaller. Set WIDTH property of label to the same as the control it is over.
Move first (left) label all the way to the left edge -- same with the bound control in detail.
Select all labels, right-click, and choose >Align > left
Do same for detail controls.
Close up extra space in each section and drag right edge of form in too.
Resize hidden control to show whole fieldname and move it to be on top of a wider control, not over the fieldname. You may have to right-click and Position >send to back for the data control so the hidden control shows.
In design view, select form by clicking in upper left where rulers intersect. On property sheet, change Default View to Continuous Forms on the Format tab.
Then:
make the form header section taller, temporarily, by moving mouse to bottom border of that section. When cursor share changes to horizontal line with double-headed vertical arrow, click and drag down.
Select the labels in the detail section, cut them, then click on form header section bar, and choose paste. Move labels to be over the respective controls. Change label captions to be friendly and make the font smaller. Best-fit so the height gets smaller. Set WIDTH property of label to the same as the control it is over.
Move first (left) label all the way to the left edge -- same with the bound control in detail.
Select all labels, right-click, and choose >Align > left
Do same for detail controls.
Close up extra space in each section and drag right edge of form in too.
Resize hidden control to show whole fieldname and move it to be on top of a wider control, not over the fieldname. You may have to right-click and Position >send to back for the data control so the hidden control shows.
select data and labels:
text Align Left (Home ribbon in Text Formatting group on right -- the buttons that are the same in Word for formatting paragraphs) the data and labels to match.
select the hidden InOut control: right-click, Position > Bring to Front.
then move it on top of ProductID on the right side (since now ProductID will be aligned Left)
You may want to center Qty or right-align it
When I left-align data/text within a control, I set Left Margin (Format tab of properties, near bottom) to 0.2 and the rest of the margins to 0. If data/text is right-aligned, I set Right Margin to 0.2 and the rest to 0. Sometimes I make margin bigger for numbers, depending on how it looks with data in it.
Now I see what you meant by both ProdLocID and LocID ... you won't need InOut if you do this.
text Align Left (Home ribbon in Text Formatting group on right -- the buttons that are the same in Word for formatting paragraphs) the data and labels to match.
select the hidden InOut control: right-click, Position > Bring to Front.
then move it on top of ProductID on the right side (since now ProductID will be aligned Left)
You may want to center Qty or right-align it
When I left-align data/text within a control, I set Left Margin (Format tab of properties, near bottom) to 0.2 and the rest of the margins to 0. If data/text is right-aligned, I set Right Margin to 0.2 and the rest to 0. Sometimes I make margin bigger for numbers, depending on how it looks with data in it.
Now I see what you meant by both ProdLocID and LocID ... you won't need InOut if you do this.
ASKER
Now I see what you meant by both ProdLocID and LocID ... you won't need InOut if you do this.
Ok I didn't exactly set the data for the second picture like that on purpose. But I am choosing to do it with both ProdLocID and LocID. I haven't deleted the IN/OUT Flag just yet. In case of change of plan. But you can't choose "From Location" from a empty source and "LocID" could provide that. "ProdLocID" is locations already with existing Products. But I am wanting to head in the direction of run a event for the (FROM/OUT) and then run a 2nd event for the (TO/IN).**BUTTON OR SOMETHING**
- First event run would Deduct the "Product ID" "Quantity" from the"FROM Location(OUT) (ProdLocID)" .
- Second event run would Increase the "Product ID" "Quantity" in the "TO Location(IN)(LocID)".
This would create 2 different records in the "ProdMovements" Table.
ASKER
If I could keep a Stock Transfer LOT ID that would be great. Stock Transfer LOT would be all the items moved on that specific LOT LIST for that Instance. With the separate runs and events I could produce a Stock Transfer LOT and print it out for a product removal FROM/OUT. Give it to so and so and say pick these items for removal. Once they are ALL removed then I can check it and then run the 2nd event for the TO/IN. This would not only be good for shelf to shelf but warehouse to warehouse where there is transit involved.
- Create Stock Transfer LOT of a removal (FROM/OUT).
- Print Stock Transfer Lot List Ticket.
- Remove and prepare all items for shipment.
- Ship items in transit to other warehouse.
- Once they receive the shipment at the other warehouse they can check the Stock Transfer LOT List Ticket. (Packing List)
- Confirm it is all there.
- They can then open Access and Find the Stock Transfer Lot List and run the 2nd Event of (IN/TO).
> "This would create 2 different records in the "ProdMovements" Table."
isn't that the table this form is based on?
for your numbered list:
figure out which tables and fields need to be updated/appended for each step, and where data needs to come from. Access can set up a Transaction with several steps such that if they don't all happen, then everything is backed out.
isn't that the table this form is based on?
for your numbered list:
figure out which tables and fields need to be updated/appended for each step, and where data needs to come from. Access can set up a Transaction with several steps such that if they don't all happen, then everything is backed out.
ASKER
isn't that the table this form is based on?YES. Just giving my thought. I may not see all the details yet but just sending thought.
figure out which tables and fields need to be updated/appended for each step, and where data needs to come from. Access can set up a Transaction with several steps such that if they don't all happen, then everything is backed out.
I just see the goal and I am unsure of the steps to get there. Its like when you start a HUGE puzzle. The goal image is there on the front of the box but how the heck do these pieces fit together. (Thank God for Puzzle Masters like yourself ;) )
Ok so far with the form I now have.
How is it able to deduct and increase quantity in the specific places?
once you figure out the tables and fields, we can help implementing the steps
ASKER
Ok I just wanted to be on the same page. Thanks.
you're welcome
even though In Out may not be needed since you are entering To and From on the same record, I would like you, as long as the hidden control is still there, to make it wide enough to show what is in it. It is very helpful to immediately see what is hidden when you are looking at the design.
you also need to set Width of label = width of data it is over
align all controls to each other (this was covered in the video link I gave you to create a form, which also has a link to my customized QAT in the video description so you can get my QAT without having to customize it yourself -- you should do this as it will save you time)
even though In Out may not be needed since you are entering To and From on the same record, I would like you, as long as the hidden control is still there, to make it wide enough to show what is in it. It is very helpful to immediately see what is hidden when you are looking at the design.
you also need to set Width of label = width of data it is over
align all controls to each other (this was covered in the video link I gave you to create a form, which also has a link to my customized QAT in the video description so you can get my QAT without having to customize it yourself -- you should do this as it will save you time)
ASKER
****1: Create Stock Transfer LOT of a removal (FROM/OUT).****
[Products.ProductID] Product
[ProdMovements.ReasonMoveI D] Reason Moving
[ProdLocations.QtyLoc] Quantity (Old Location)
[ProdLocations.ProdLocID] From Location
****2: Print Stock Transfer Lot List Ticket.****
Print the ticket. I don't know exactly if I need some type of ProdMoveLotID in the ProdMovements table and then create another table called ProdMovementLots....OR just a field for a Lot Number identifier Flag...
****3: Remove and prepare all items for shipment.****
Physical Labor
****4: Ship items in transit to other warehouse.****
Mechanical Labor
****5: Once they receive the shipment at the other warehouse they can check the Stock Transfer LOT List Ticket. (Packing List)****
Physical Labor
****6: Confirm it is all there. *****
Physical Labor
****7: They can then open Access and Find the Stock Transfer Lot List and run the 2nd Event of (IN/TO).****
[Products.ProductID] Product
[ProdMovements.ReasonMoveI D] Reason Moving
[ProdLocations.QtyLoc] Quantity (New Location)
[Locations.LocID] To Location
STEPS 1 and 7 these field values MUST BE THE SAME:
[Products.ProductID] Product
[ProdMovements.ReasonMoveI D] Reason Moving
[ProdLocations.QtyLoc] Quantity (New Location)
All is aligned:
[Products.ProductID] Product
[ProdMovements.ReasonMoveI
[ProdLocations.QtyLoc] Quantity (Old Location)
[ProdLocations.ProdLocID] From Location
****2: Print Stock Transfer Lot List Ticket.****
Print the ticket. I don't know exactly if I need some type of ProdMoveLotID in the ProdMovements table and then create another table called ProdMovementLots....OR just a field for a Lot Number identifier Flag...
****3: Remove and prepare all items for shipment.****
Physical Labor
****4: Ship items in transit to other warehouse.****
Mechanical Labor
****5: Once they receive the shipment at the other warehouse they can check the Stock Transfer LOT List Ticket. (Packing List)****
Physical Labor
****6: Confirm it is all there. *****
Physical Labor
****7: They can then open Access and Find the Stock Transfer Lot List and run the 2nd Event of (IN/TO).****
[Products.ProductID] Product
[ProdMovements.ReasonMoveI
[ProdLocations.QtyLoc] Quantity (New Location)
[Locations.LocID] To Location
STEPS 1 and 7 these field values MUST BE THE SAME:
[Products.ProductID] Product
[ProdMovements.ReasonMoveI
[ProdLocations.QtyLoc] Quantity (New Location)
All is aligned:
Hi Dustin,
I am confused by your steps.
is (1) creating the record?
this is not updating anything in ProdLocations -- at least not yet.
ProdLocations:
1. should not have SkuID. To delete that field, first you will need to delete the relationship.
2. make sure there is a UNIQUE INDEX on the COMBINATION of ProductID and LocID
3. Rearrange fields in table design to show those with unique index above the PK.
4. Stretch fieldlist on relationships diagram to show everything.
On ALL tables, put fields that have, or are part of, a unique index above PK
Put all other key fields at the top under PK and rearrange to minimize join lines crossing
Size (make bigger/smaller) all fieldlists to everything shows, but not any extra space. Since tracking fields are in all tables (dtmAdd, dtmEdit, IDadd, IDedit), it is ok to now show those -- make sure they are always at the bottom.
2: Print Stock Transfer Lot List Ticket
Design the report to show all records. When OpenReport is used to open it in code, a Where clause can be specified to limit the records.
writing the steps help you better define the process for automation -- step 7 is wrong since everything is one the same record.
While you can split In/Out for viewing, it will not be done with an InOut field since From and To are on the same record in ProdMovements ... we can discuss this later
> "All is aligned"
the data for FROM and To does not match the width of the labels
I am confused by your steps.
is (1) creating the record?
this is not updating anything in ProdLocations -- at least not yet.
ProdLocations:
1. should not have SkuID. To delete that field, first you will need to delete the relationship.
2. make sure there is a UNIQUE INDEX on the COMBINATION of ProductID and LocID
3. Rearrange fields in table design to show those with unique index above the PK.
4. Stretch fieldlist on relationships diagram to show everything.
On ALL tables, put fields that have, or are part of, a unique index above PK
Put all other key fields at the top under PK and rearrange to minimize join lines crossing
Size (make bigger/smaller) all fieldlists to everything shows, but not any extra space. Since tracking fields are in all tables (dtmAdd, dtmEdit, IDadd, IDedit), it is ok to now show those -- make sure they are always at the bottom.
2: Print Stock Transfer Lot List Ticket
Design the report to show all records. When OpenReport is used to open it in code, a Where clause can be specified to limit the records.
writing the steps help you better define the process for automation -- step 7 is wrong since everything is one the same record.
While you can split In/Out for viewing, it will not be done with an InOut field since From and To are on the same record in ProdMovements ... we can discuss this later
> "All is aligned"
the data for FROM and To does not match the width of the labels
ASKER
is (1) creating the record?YES it is removing product from the shelves and this creates a record in the prodmovements table . Then in a chain reaction this record will deduct quantity from the specific location in the ProdLocations table.
Seperate Record for each Movement IN or OUT and a Stock Transfer LOT to show the records together in an instance.
should not have SkuID. To delete that field, first you will need to delete the relationship.
I was using this for a query to return all the Products (SKUs with a condition) with all their corresponding ProdLocation. Show full quantity and all locations of a SKU. Just only thing I could come up with. But I think I was doing it wrong anyways I just hadden changed it back yet.
2. make sure there is a UNIQUE INDEX on the COMBINATION of ProductID and LocIDDone
3. Rearrange fields in table design to show those with unique index above the PK.
4. Stretch fieldlist on relationships diagram to show everything.
I am working on the rest now
ASKER
the data for FROM and To does not match the width of the labelsYeah I don't know but if you look at the photo above the orange highlighted box shows it is aligned but part is invisible.
ASKER
I can't add a unique Index on my ProductImages URL???
> "Yeah I don't know but if you look..."
it is best to get into the habit of matching widths -- easier to align and, therefore, quicker. You can adjust text within a control using Left Margin and Right Margin or just CENTER label and data if that is what you want.
> "Seperate Record for each Movement IN or OUT and a Stock Transfer LOT to show the records together in an instance."
in will be the product, qty, and LocID
out will be the product, qty, and ProdLocID (which resolves to LocID)
.. at least according to how you have your structure set up
> "chain reaction"
code will need to be written to do this.
it is best to get into the habit of matching widths -- easier to align and, therefore, quicker. You can adjust text within a control using Left Margin and Right Margin or just CENTER label and data if that is what you want.
> "Seperate Record for each Movement IN or OUT and a Stock Transfer LOT to show the records together in an instance."
in will be the product, qty, and LocID
out will be the product, qty, and ProdLocID (which resolves to LocID)
.. at least according to how you have your structure set up
> "chain reaction"
code will need to be written to do this.
ASKER
All my controls match the width of their corresponding Label above.
ok -- the screen shot looks like they didn't
create the report you will want (for all records, knowing it will be limited by specifying Where). I need to get back to my work now -- will check back in later
create the report you will want (for all records, knowing it will be limited by specifying Where). I need to get back to my work now -- will check back in later
ASKER
in will be the product, qty, and LocID
out will be the product, qty, and ProdLocID (which resolves to LocID)
True which I think about it. The IN will have to update ProdLocID not LocID
****7: They can then open Access and Find the Stock Transfer Lot List and run the 2nd Event of (IN/TO).****
[Products.ProductID] Product
[ProdMovements.ReasonMoveID] Reason Moving
[ProdLocations.QtyLoc] Quantity (New Location)
[Locations.LocID] To Location
[Locations.LocID] To Location IS WRONG SORRY
it would be: [ProdLocations.ProdLocID] To Location
so....
****7: They can then open Access and Find the Stock Transfer Lot List and run the 2nd Event of (IN/TO).****
[Products.ProductID] Product
[ProdMovements.ReasonMoveI
[ProdLocations.QtyLoc] Quantity (New Location)
[ProdLocations.ProdLocID] To Location
Ok Thanks
> "The IN will have to update ProdLocID not LocID"
actually, IN will have to update data for record of ProdLocID too -- and add a record if one is not already there for combination of ProdID + LocID
some people will tell you NOT to store calculations/calculated fields -- but I like to store them when not doing do would impact performance; provided you can rebuild the numbers anytime -- which is why tracking what goes into the calculation is important.
"which I think about it"
yes ... you need to think about where the data is ...
I am skipping 7 for now as that needs better thinking too
actually, IN will have to update data for record of ProdLocID too -- and add a record if one is not already there for combination of ProdID + LocID
some people will tell you NOT to store calculations/calculated fields -- but I like to store them when not doing do would impact performance; provided you can rebuild the numbers anytime -- which is why tracking what goes into the calculation is important.
"which I think about it"
yes ... you need to think about where the data is ...
I am skipping 7 for now as that needs better thinking too
ASKER
actually, IN will have to update data for record of ProdLocID too -- and add a record if one is not already there for combination of ProdID + LocID
On the Dot! YES!
I am working on the reports now. (Learning and trying to LOL) But here is a updated screenshot of my relationships. I have expanded them only up to the dtm fields.
on the report: design it for the user. If the report will be sorted, put the sort column(s) first and then the most important columns to whoever will be looking at the reports. If the ID is on there at all where it is visible, it should be less prominent -- maybe color it and its label gray too.
You can copy controls from form designs to report designs -- however, for reports, you should join the tables that go into the comboboxes and use textboxes.
On the joins in the report Record Source (DATA tab of properties -- click builder button ... to change): right-click on join line and choose to show all records from ProdMovements table and just those from related table where they match so if something is not filled out, the record will still show.
Format date/time as:
mm-dd-yy h:nn ampm, ddd
seconds won't matter to anyone but the day might.
You will probably also want to left-align this information.
On a report, you will want to right-align numbers like Qty and put a sum in group and report footer.
=sum( qty )
set the format property to: #,##0
left-align text
align labels the same as the data they are over -- just as with forms, make them the same width and use Left Margin and Right Margin to add spacing
for the count:
=format( count(*),"#,##0") & " records listed"
Move the report title out of the report header to the page header. I like to combine date, time, and page and put that all in the page header on the right:
="Printed " & format(Now(), "mm-dd-yy h:nn ampm, ddd") & ", Page " & [page] & "/" & [pages]
then you don't need the page information in the page footer.
cute report name
here is an EE video on designing reports:
Create a Query and Grouped Report and Modify Design using Access
https://www.experts-exchange.com/videos/4514/Create-a-Query-and-Grouped-Report-and-Modify-Design-using-Access.html
you can keep the report you have -- just change Record Source (join in other tables and choose corresponding text field. for Sku+CondCode, make a calculated field and call it ProductSku or something like that) and then for each control that is currently a combo:
right-click and Change to > textbox
change Control Source
copy Control Source to Name
for controls with data, set Can Grow to yes (near bottom of properties on Format tab of properties)
click the Group & Sort button on Design ribbon -- you at least want to sort as this report will be used for different purposes, in addition to being used to display one record on demand
You can copy controls from form designs to report designs -- however, for reports, you should join the tables that go into the comboboxes and use textboxes.
On the joins in the report Record Source (DATA tab of properties -- click builder button ... to change): right-click on join line and choose to show all records from ProdMovements table and just those from related table where they match so if something is not filled out, the record will still show.
Format date/time as:
mm-dd-yy h:nn ampm, ddd
seconds won't matter to anyone but the day might.
You will probably also want to left-align this information.
On a report, you will want to right-align numbers like Qty and put a sum in group and report footer.
=sum( qty )
set the format property to: #,##0
left-align text
align labels the same as the data they are over -- just as with forms, make them the same width and use Left Margin and Right Margin to add spacing
for the count:
=format( count(*),"#,##0") & " records listed"
Move the report title out of the report header to the page header. I like to combine date, time, and page and put that all in the page header on the right:
="Printed " & format(Now(), "mm-dd-yy h:nn ampm, ddd") & ", Page " & [page] & "/" & [pages]
then you don't need the page information in the page footer.
cute report name
here is an EE video on designing reports:
Create a Query and Grouped Report and Modify Design using Access
https://www.experts-exchange.com/videos/4514/Create-a-Query-and-Grouped-Report-and-Modify-Design-using-Access.html
you can keep the report you have -- just change Record Source (join in other tables and choose corresponding text field. for Sku+CondCode, make a calculated field and call it ProductSku or something like that) and then for each control that is currently a combo:
right-click and Change to > textbox
change Control Source
copy Control Source to Name
for controls with data, set Can Grow to yes (near bottom of properties on Format tab of properties)
click the Group & Sort button on Design ribbon -- you at least want to sort as this report will be used for different purposes, in addition to being used to display one record on demand
ASKER
I was just thinking (Dangerous I know) but in order to make and keep seperate records of IN/OUT for the prodmovements table. would it be a good Idea to create two extra tables IN and OUT with their PK to FK in the prodmovement table. Then the Prodmovement table would be the STILL info. ProdLocations would then have a FK to the Out Table and Locations would have a FK to the IN Table.....just a thought
in my opinion, not if you are going to have records with fields having the same basic information. While I can appreciate the desire to track In/Out as separate transactions, you would still want to synchronize them (so store field they relate to) and that just doubles the records in a table that will already be big. In/Out can already be determined using this structure -- just a difference of opinion and style.
I saw your other question too -- I would probably add a LOCID=0, LocNm="Fill Order" (or something like that) to the locations table using an append query so you can get 0 to be the autonumber value
I saw your other question too -- I would probably add a LOCID=0, LocNm="Fill Order" (or something like that) to the locations table using an append query so you can get 0 to be the autonumber value
ASKER
ok I am not 100% sure about Append Queries. I have successfully ran them but not 100% clear. Append Queries are to add to records to tables right. Where update queries update existing records in a table.
yes
both types of queries are covered in my Access Basics book:
http://accessmvp.com/strive4peace
to create an Append Query with VALUES, the easiest way is to make a select query using the Locations table. Put LocID and LocNm on the grid. Change the query type to an Append query and choose Locations as the table to append to. Because the fields have the same name as something in the table, of course, the Append to cell will be filled out for each column.
Then change what is in the Field cells on the first row to:
0 -- in the column that appends to LocID
"fill order"-- in the field cell that appends to LocNm
then click on the fieldlist and DELETE it
then, with no fieldlist, run the query by clicking !
only ONE row should be appended
both types of queries are covered in my Access Basics book:
http://accessmvp.com/strive4peace
to create an Append Query with VALUES, the easiest way is to make a select query using the Locations table. Put LocID and LocNm on the grid. Change the query type to an Append query and choose Locations as the table to append to. Because the fields have the same name as something in the table, of course, the Append to cell will be filled out for each column.
Then change what is in the Field cells on the first row to:
0 -- in the column that appends to LocID
"fill order"-- in the field cell that appends to LocNm
then click on the fieldlist and DELETE it
then, with no fieldlist, run the query by clicking !
only ONE row should be appended
ASKER
You can copy controls from form designs to report designs -- however, for reports, you should join the tables that go into the comboboxes and use textboxes.
On the joins in the report Record Source (DATA tab of properties -- click builder button ... to change): right-click on join line and choose to show all records from ProdMovements table and just those from related table where they match so if something is not filled out, the record will still show.
When I do this it turns my User Visual Names (Captions) back into just Autonumber IDs
then click on the fieldlist and DELETE it
Not sure what you meant there but this is what I have:
INSERT INTO Locations ( LocID, LocNm )
SELECT 0 AS Expr1, [FillOrder] AS Expr2
FROM Locations;
> "When I do this it turns my User Visual Names (Captions) back into just Autonumber IDs"
do not define Captions for fields in the table design -- this will bite you
instead, join in the tables you need
The Display Control should also NOT be a combo or listbox in the table design for a field (unless you are building a web database, which you are not) ... that will bite too
if you followed all the instructions, this is what you should have:
do not define Captions for fields in the table design -- this will bite you
instead, join in the tables you need
The Display Control should also NOT be a combo or listbox in the table design for a field (unless you are building a web database, which you are not) ... that will bite too
if you followed all the instructions, this is what you should have:
INSERT INTO Locations ( LocID, LocNm )
SELECT 0 AS Expr1, "Fill Order" AS Expr2
delete the Locations fieldlist from the query after filling out the grid, or you will get a row for every row it has, and that will cause errors when it runs. "Fill Order" needs to be dlimited with quotes since it is literal text
ASKER
"When I do this it turns my User Visual Names (Captions) back into just Autonumber IDs"
do not define Captions for fields in the table design -- this will bite you
instead, join in the tables you need
I knew that was going to happen LOL. I didn't know what else to call it. But I have NO CAPTIONS in my tables at all. I have double and tripled checked. But anyways when in a combo box and you set column count 2 Column width 0;1.5" Bound to column 1. Well the in the report if it is a combobox I can see the (Lets Use ReasonMoveID) ReasonMoveID =8 = Shelf Condense...In combobox I can see the text SHELF Condense....with a textbox it goes back to 8. As in ReasonMoveID 8.
delete the Locations fieldlist
Ok I see it took me a minute but Locations fieldlist you mean the table in the viewer area above the grid???
here are the most important properties to pay attention to for comboboxes:
1. Name
2. Control Source
3. Row Source
4. Column Count
5. Column Widths
6. List Width
> "Well the in the report if it is a combobox "
anyway, better not to use comboboxes in the report. Instead, modify the Record Source, join* in the needed tables, and add the text fields to the grid.
* be sure to change join type to show ALL records from the main table and just those from related table where they match
then change Control Type to textbox, Control Source to the text field, and the Name to match
(I already told you this...)
> "table in the viewer area above the grid???"
yes. once everything is the Field cell is a specified value, the table is no longer needed. I find it easier to put it there to get the fields, then delete it after the fields are changed.
1. Name
2. Control Source
3. Row Source
4. Column Count
5. Column Widths
6. List Width
> "Well the in the report if it is a combobox "
anyway, better not to use comboboxes in the report. Instead, modify the Record Source, join* in the needed tables, and add the text fields to the grid.
* be sure to change join type to show ALL records from the main table and just those from related table where they match
then change Control Type to textbox, Control Source to the text field, and the Name to match
(I already told you this...)
> "table in the viewer area above the grid???"
yes. once everything is the Field cell is a specified value, the table is no longer needed. I find it easier to put it there to get the fields, then delete it after the fields are changed.
ASKER
then change Control Type to textbox, Control Source to the text field, and the Name to match
(I already told you this...)
I will retry this. I did that but I don't know. I will see.
when you are looking at table design, click in a field that automatically gives you a combobox.
on the bottom, next to the General tab, you see a Lookup tab.
On the lookup tab is Display Control ... these should NOT be set to combo or listbox
if you used the wizard to make lookup fields, they will be set ... and in the future, don't use the wizard to make ANY fields
on the bottom, next to the General tab, you see a Lookup tab.
On the lookup tab is Display Control ... these should NOT be set to combo or listbox
if you used the wizard to make lookup fields, they will be set ... and in the future, don't use the wizard to make ANY fields
ASKER
Correct and I have no lookups in any of my tables. None! I even checked three times and went through every table slowly and also made sure there were no captions. Thank you for your help.
you're welcome
once the report uses all textboxes (or checkboxes), set the Sorting & Grouping. Don't put any criteria or sort options in the underlying record source. Use the Report Sorting & Grouping to specify that.
once the report uses all textboxes (or checkboxes), set the Sorting & Grouping. Don't put any criteria or sort options in the underlying record source. Use the Report Sorting & Grouping to specify that.
ASKER
Ok I am going to use one control field out of the report. I am choosing ReasonMoveID. Currently ReasonMoveID is a combobox.
SQL:
Do you see anything wrong with the SQL?
Once I change the combobox to a text box it turns back into 8 instead of the text "Shelf Condense".
No hurry for an answer.
SQL:
SELECT ProdMovements.ReasonMoveID, ReasonMove.ReasonMoveNm
FROM ReasonMove RIGHT JOIN ProdMovements ON ReasonMove.ReasonMoveID = ProdMovements.ReasonMoveID;
Do you see anything wrong with the SQL?
Once I change the combobox to a text box it turns back into 8 instead of the text "Shelf Condense".
No hurry for an answer.
SELECT ReasonMoveID, ReasonMoveNm
FROM ReasonMove
ORDER BY ReasonMoveNm;
> "it turns back into 8"
read what I wrote above, "here are the most important properties to pay attention to for comboboxes:"
ASKER
Thanks
if you are going to do In and Out as separate processes then you should only store one LocID and instead store InOut with, for instance, -1 for out and 1 for In.
> "There will be 4 fields in this form"
a form does not have fields; it has controls.
The Control source of a control can be a field
The Record source for a form determines where the data is stored.