Solved

MS Access Product Inventory Movement Transfer Form With Separate Records Combined Into one Form

Posted on 2016-10-12
55
60 Views
Last Modified: 2016-10-19
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:
  1. ProductID (Found In the Products Table)
  2. QtyMove
  3. LocID (Found In the Locations Table) (Caption: From Location)
  4. 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)
Run1 = Decrease (Form.Quantity) from (Form.FromLocation) for (Form.ProductID)

  • (For Inventory Transfer To)
Run2 = Increase (Form.Quantity) To (Form.ToLocation) for (Form.ProductID)


**ReasonMove Table**

  1. ReasonMoveID = 14 = Transfer Out
  2. 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

db82.jpg
0
Comment
Question by:Dustin Stanley
  • 31
  • 24
55 Comments
 
LVL 19
ID: 41840863
Will the locations for IN and Out be specified at the same time? This detmines if you will store LocID1 and LocID2 on a record ... or
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.
0
 

Author Comment

by:Dustin Stanley
ID: 41840880
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
0
 

Author Comment

by:Dustin Stanley
ID: 41840928
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
0
 
LVL 19
ID: 41841099
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
0
 

Author Comment

by:Dustin Stanley
ID: 41841149
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.
0
 
LVL 19

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 500 total points
ID: 41841208
make the form* and then I will give you a code example. Remember to set each control NAME (1st property on Other and All tabs of Properties) equal to the Control Source (Data tab of Properties) -- the Name is what is used in code to refer to each control and this makes things a lot easier.  

Some people will recommend that you name controls by their type (like txtOrderID) but I don't bother with this as it is an extra thing to remember and Access is very good at sorting out whether you are referring to a control or a field.

When the form is done (just do it for one way and size it so that 2 of them will be side-by-side on a mainform), paste a screenshot of the form design

InOut should be on the form but not visible. In the Design view, set Visible property (Format tab of Properties) to No and to make it more obvious, set the back color to be dark and the font color to be light.

Here is a video to create a form:
https://www.youtube.com/watch?v=norAB9QvIls

this example is for a continuous form but you will be making a Single Form
0
 

Author Comment

by:Dustin Stanley
ID: 41841216
Great Thanks!
0
 

Author Comment

by:Dustin Stanley
ID: 41841223
By the way I have to have a continuous form.
0
 
LVL 19
ID: 41841226
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.
0
 

Author Comment

by:Dustin Stanley
ID: 41841230
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.
0
 
LVL 19
ID: 41841235
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.
0
 

Author Comment

by:Dustin Stanley
ID: 41841261
ok I will work on it tomorrow thanks.
0
 

Author Comment

by:Dustin Stanley
ID: 41843035
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:
  • 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]

Open in new window


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:
db83.jpgdb84.jpgdb85.jpg
0
 
LVL 19
ID: 41843045
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.
0
 

Author Comment

by:Dustin Stanley
ID: 41844548
Ok I had some time to get this for you.

db86.jpg
0
 
LVL 19
ID: 41845013
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:
me.dtmAdd=Now()

Open in new window


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?
0
 

Author Comment

by:Dustin Stanley
ID: 41845030
No Layout

Everything is done.

I do want a continuous form but figured I would do this one first to understand and then go from there. I didn't know how to lay it out. I think the continuous form would work best seeing how sometimes up to hundreds of items my be moved at the same time.

db87.jpg
0
 
LVL 19
ID: 41845044
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.
0
 

Author Comment

by:Dustin Stanley
ID: 41845061
OK Done.

db88.jpgdb89.jpg
0
 
LVL 19
ID: 41845068
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.
0
 

Author Comment

by:Dustin Stanley
ID: 41845083
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**

  1. First event run would Deduct the "Product ID" "Quantity" from the"FROM Location(OUT) (ProdLocID)" .
  2. 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.

90.jpg
0
 

Author Comment

by:Dustin Stanley
ID: 41845088
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.

  1. Create Stock Transfer LOT of a removal (FROM/OUT).
  2. Print Stock Transfer Lot List Ticket.
  3. Remove and prepare all items for shipment.
  4. Ship items in transit to other warehouse.
  5. Once they receive the shipment at the other warehouse they can check the Stock Transfer LOT List Ticket. (Packing List)
  6. Confirm it is all there.
  7. They can then open Access and Find the Stock Transfer Lot List and run the 2nd Event of (IN/TO).
0
 
LVL 19
ID: 41845104
> "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.
0
 

Author Comment

by:Dustin Stanley
ID: 41845118
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?
0
 
LVL 19
ID: 41845121
once you figure out the tables and fields, we can help implementing the steps
0
 

Author Comment

by:Dustin Stanley
ID: 41845126
Ok I just wanted to be on the same page. Thanks.
0
 
LVL 19
ID: 41845134
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)
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:Dustin Stanley
ID: 41845146
****1: Create Stock Transfer LOT of a removal (FROM/OUT).****
[Products.ProductID] Product
[ProdMovements.ReasonMoveID] 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.ReasonMoveID] 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.ReasonMoveID] Reason Moving
[ProdLocations.QtyLoc] Quantity (New Location)

All is aligned:
   db90.jpg
0
 
LVL 19
ID: 41845164
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
0
 

Author Comment

by:Dustin Stanley
ID: 41845171
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 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.
Done

I am working on the rest now
0
 

Author Comment

by:Dustin Stanley
ID: 41845177
the data for FROM and To does not match the width of the labels
Yeah I don't know but if you look at the photo above the orange highlighted box shows it is aligned but part is invisible.
0
 

Author Comment

by:Dustin Stanley
ID: 41845190
I can't add a unique Index on my ProductImages URL???
0
 
LVL 19
ID: 41845191
> "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.
0
 

Author Comment

by:Dustin Stanley
ID: 41845197
All my controls match the width of their corresponding Label above.
0
 
LVL 19
ID: 41845202
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
0
 

Author Comment

by:Dustin Stanley
ID: 41845204
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.ReasonMoveID] Reason Moving
[ProdLocations.QtyLoc] Quantity (New Location)
[ProdLocations.ProdLocID] To Location



Ok Thanks
0
 
LVL 19
ID: 41845218
> "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
0
 

Author Comment

by:Dustin Stanley
ID: 41845253
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.

db91.jpg
0
 

Author Comment

by:Dustin Stanley
ID: 41845281
Ok!
 
db96.jpgdb97.jpgdb94.jpgdb95.jpg
0
 
LVL 19
ID: 41845666
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
0
 

Author Comment

by:Dustin Stanley
ID: 41845829
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
0
 
LVL 19
ID: 41845833
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
0
 

Author Comment

by:Dustin Stanley
ID: 41845835
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.
0
 
LVL 19
ID: 41845840
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
0
 

Author Comment

by:Dustin Stanley
ID: 41848527
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;

Open in new window

0
 
LVL 19
ID: 41848574
> "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:
INSERT INTO Locations ( LocID, LocNm )
SELECT 0 AS Expr1, "Fill Order" AS Expr2

Open in new window

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
0
 

Author Comment

by:Dustin Stanley
ID: 41848687
"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???
0
 
LVL 19
ID: 41848711
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.
0
 

Author Comment

by:Dustin Stanley
ID: 41848726
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.
0
 
LVL 19
ID: 41848754
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
0
 

Author Comment

by:Dustin Stanley
ID: 41848759
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.
0
 
LVL 19
ID: 41848771
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.
0
 

Author Comment

by:Dustin Stanley
ID: 41848793
Ok I am going to use one control field out of the report. I am choosing ReasonMoveID.  Currently ReasonMoveID is a combobox.

SQL:
SELECT ProdMovements.ReasonMoveID, ReasonMove.ReasonMoveNm
FROM ReasonMove RIGHT JOIN ProdMovements ON ReasonMove.ReasonMoveID = ProdMovements.ReasonMoveID;

Open in new window



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.
0
 
LVL 19
ID: 41849347
SELECT ReasonMoveID, ReasonMoveNm
FROM ReasonMove
ORDER BY ReasonMoveNm;

Open in new window


> "it turns back into 8"
read what I wrote above, "here are the most important properties to pay attention to for comboboxes:"
0
 

Author Closing Comment

by:Dustin Stanley
ID: 41850809
Thanks
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now