Hello. I'm trying to design a simple database for a bicycle recycling charity. I hit a brick wall when realising that I had never before attempted to update a record. :eek: Until now I've just used Access to add new members to a bus user's organisation.
If the shop takes delivery of 20 tyres, I need to add 20 to the current stock.
Is it possible to achieve a simple Form that adds to or subtracts a quantity from current stock, or do I have to learn some access programming?
I've looked through some textbooks and can't find a simple update solution.
I have an inventory of equipment that is assigned to different locations. When repair is needed, the equipment is brought in and swapped with a loaner. How can I update on a form the location change from "A" to "Shop" that will show up on the related equipment table? I have "Location" in the table, but don't know if I need something to the effect of "NewLocation" to assist in showing the equipment swap.
Hi, I was asked to help create a very simple Access DB for a small restaurant to help them keep track of inventory.
The only things that the owner wants to keep track of are: 1.Item Catalog 2.Date and Quantity of Item IN or OUT 3.Current # of Items
My problem here is that there are 5 Item categories, each with a relatively lost list of Items under each one. 1.Drinks 2.Ice Cream 3.Kitchen Supplies 4.Grocery 5.Etc.
Will it be advisable for me to create an Item Catalog TABLE for each of the category instead of piling all the Items into just one Item Catalog TABLE?
The Designs I’m currently considering are:
A.)
TABLE SET 1 : Catalog of Items Fields (ID, Name) •TABLE Drinks •TABLE Ice Cream •TABLE Kitchen Supplies •TABLE Grocery •TABLE ETC --------------------------------------------------- TABLE SET 2 : Movement IN/OUT of Items Fields (Movement ID, Name, Date, IN-Amount, OUT-Amount) •TABLE Drinks •TABLE Ice Cream •TABLE Kitchen Supplies •TABLE Grocery •TABLE ETC
B.) TABLE 1: Catalog of Items Fields (ID, Category, Name)
TABLE 2: Movement IN/OUT of Items Fields (Movement ID, Name, Category, Date, IN-Amount, OUT-Amount)
I’m really more inclined to choice A because I just re-learned Access last month and will be creating my first serious database just now. I don’t want to choose a more complex table structure (choice B) that might cause problems with the DB later (as it will really be used to keep track of real-life inventory and might screw up their operations if it goes haywire). Is Design A viable? Or flawed?
Another question I have is with the structure of the Movement – IN/OUT table. Is this workable? My thought is just to let the user enter something like:
In the database, then compute current Quantity of the Item using a query (Sum[IN] – Sum[OUT]) . Will this work? I’m a bit apprehensive with this because I’m thinking I should have another TABLE that will store the actual inventory Quantity instead of the Quantity just being computed through query. But doing another TABLE for Quantity does complicate stuff a lot since it will have to determine which Quantity value gets added to or subtracted from based on many criteria. Will it be OK just to use the query to compute Quantity left?
I'm not sure what the best way to set this up is, and I'm hoping someone can point me in the right direction.
I have a long list of different parts that I have acquired and taken inventory of. I am selling kits that contain different amounts of certain parts. For example, Kit 1 contains 2 of part A, 3 of part B, and 14 or part L. I will only be selling complete kits and not the individual parts.
Here's what I'm thinking. I need a table for the individual parts that lists the quantities of each. A table for the kits and a subtable for the kits that lists the quantities of each part. A transaction table.
Then I just need a form that has a combo box with the kits listed, date of transaction and quantity shipped, and a button that runs a query to subtract from the quantities of the individual parts based on the kits table and subtable.
Can someone please help? I'd really appreciate any tips.
I am trying to create a simple store keeping In and Out inventory database using Access, I thought I had made it but looks like I am missing something here.
The store works on SRV (Store Receiving Voucher) and SIV (Store Issue Voucher). Products will be added based on SRV and will be issued out based on SIV. So far I have created the tables as you can see in the figure. One thing I am not understanding is where to keep the record of the Current Quantity of each product, lets say an Item has been added or issue out, it should be added or deducted accordingly from that specific products overall quantity. Right now I have a sample field within products table as you can see with the name QtyOnHand but that doesn't seem to be logical.
I am trying to update the value in one field (RUS Prop Class Document) of a form based upon what is selected in another field (RUS Prop Class Code). I plan on this being a hidden field so I was creating an after update expression in [RUS Prop Class Code]. My problem is that the value returned is the very nice and working query but not the value of the query. I'm missing something that I think is simple but cannot figure out with my limited resources and late hours.
Here is what I have for the after update expression in [RUS Prop Class Code]:
Private Sub RUS_Prop_Class_Code_AfterUpdate() [RUS Document].Value = "Select [tbl_List of RUS Classifications].[RUS Prop Class Document] " & _ "FROM [tbl_List of RUS Classifications] " & _ "WHERE [tbl_List of RUS Classifications].[RUS Prop Class ID] =" & Me.[RUS Prop Class Code] & ";" End Sub
Hi... I searched quick, didn't find anythign, but you guys are so responsive... :D
Question: Part 1. On TimeSheet form, I want to click to "create new job", and the Jobs Form opens, at a new record. Currently it opens at record 1.
Part 2. I foresee that when I return from the modal job form, the old form (timesheet) would not have updated the combobox to show the jobname I just entered.
I guess this is what subforms are, because I keep reading about them, but dunno what they are.
I have our primary web based inventory system that I am exporting to Excel and using this as an import to Access for the main raw data for my database. This being inventory it changes daily so I am updating this table every day. When I try to append the table it ads all the records. I am wanting an easy way to add only the new records/take out the ones that are no longer there. Basically update the table with what is currently there.The only have I have found to do this is by running non-matching queries and update queries.
I want to create a form that allows users to update multiple fields for multiple assets. Below is what I came up with:
Ideally, I'd like the subform to be filled in by having the user select multiple Assets from the S/N combobox field which would then auto-populate the "Type" field. Then they would fill out the appropriate fields they want edited in the top part of the form. They hit save and magic happens. This would also be nice because only assets they want edited would be displayed (easier on the eyes) and no distinguishing would be necessary. To do it this way, I know I would need to use a temp table but I wanna avoid using temp tables.
I know I can do this by adding a Yes/No field in the "Asset" table, setting the "Asset" table as the subform's recordsource, and then putting a checkbox in the subform and allowing them to check the assets that they want to edit (which would also allow me to sort it instantly so that checked Assets are at the top of the datasheet for easy viewing), but I would like to know if there's a way of accomplishing this without the use of checkboxes.
I know I could also use a listbox and that allows them to multi-select items, but I'm not sure if that allows me to group all selected items at the top of the listbox for easy viewing of selected items. Plus it would involve a lot of scrolling (there are over 2k assets).
I am a very new Access user. Just getting into it. I have created a database and want to do something that seems simple to me but I can't figure it out.
I have a database containing membership information for a local fishing club.
I want to be able to create a situation where I can do the following.
Enter a membership number and have the members info displayed so that I can then make any changes and update it. I want to be able to do this one member at a time so that I can update as changes come in.
I've created a database of equipment we keep in a vault. Every so often the boss wants a count of items by model. I would like to create a button on the switchboard that can bring up this count for him.
First I have created a query which will count by model. The SQL of that query is here: SELECT Count(*) AS Expr1 FROM [Count Query] WHERE ((([County Query].[Model]) Like "nnn"));
(nnn is the model number I will want and will have a diff query for each model.) Now, I'm trying to get these into a form for the boss to simply click. When I do, I get the #Name? error. What am I doing wrong here?
I see a lot of really interesting ways to do this via code as an automated action, but what I can't seem to find is how to do this manually?? I will look into the automation at a later time and probably use some of these wonderful ideas. For the moment, however, I just need to know a simple way to update my front end table after I have redesigned it in the back end. MSAccess help files can't seem to point me in the right direction. I've already changed my back end table, but can seem to make those changes reflect in the front end. I would appreciate any help with this, thanks.
Hello, I am still wet behind the ears in regard to Access so your help and support is much needed.
I have created a form in Access where i am having trouble with a textbox. In short this box is called [Shift] i want it to be populated by the result the system returns in another Textbox which is called [Time], this box is set up as Time(). So below is what i need it to do when the Form is used.
Textbox: [Time] = (07:00 - 14:59) would return "Earlies" in Textbox: [Shift] Textbox: [Time] = (15:00 - 22:59) would return "Lates" in Textbox: [Shift] Textbox: [Time] = (23:00 - 06:59) would return "Nights" in Textbox: [Shift]
I have tried the VBA below without success in this task so i can only assume it is due to the fact that the Time field is system generated or the fact it is in time format:
I've done this tons of times, so I don't understand what's happening... The title says it all: when I update the ID of one record of the main table, the referenced records on related tables won't update even though they have a relationship between them with the option "Cascade update" clicked.
In the attached database, the main table "Expedientes" is related to table "Actuaciones".
I'm trying to use an unbound form to enter single inventory items to my database. As part of the function of this form, I'd like to check new items against the current inventory, and perform the function you can see in the error handling code(Which works fine). Currently, when I try to run this I get an error on the syntax of the 'FROM' clause.
Option Compare Database Option Explicit Private Sub btnCommit_Click() 'On Error GoTo errHandler Dim SQL As String SQL = " INSERT INTO [InventoryDetail]" & _
I have a form that displays a stock inventory, one of the fields I use is called [ItemCode].
On the Stock_Inventory form I have 10 command buttons that open other forms to display additional product information.
These were previously sub_forms that sat in a tabbed control box within the Stock_Inventory Form but as it now takes a while for the form to load (lots of queries running each time you search for a product) users are getting slightly frustrated.
So, what I decided to do, was move each subform from the tabbed control into a new form and open that form when it is needed by the user.
OK, when I open Stock_Inventory and search for a product [ItemCode = "ABC123"] and then want to see the Sales_History, I have to open my new form, Stock_Inventory_Sales_History, within this form are two sub-forms, each subform has a field called [ItemCode].
However, when Stock_Inventory_Sales_History opens it asks me to input the parameter [ItemCode] twice, I presume this is because the Stock_Inventory_Sales_History does not have a record source and that the subforms need [ItemCode="ABC123"] to run each query and return the data.
I need to pass the [ItemCode] from the Stock_Inventory form to each of the 2 Subforms that are on the Stock_Inventory_Sales_History form automatically.
Code that will allow me to post selective data from a purchase received form to the inventory using a command button. this command should also add to the existing quantity in the inventory.
I've got a continuous form that displays my massive 3,000+ inventory, with the option to filter down to specific categories. Some categories have 5 items, others have hundreds. The design I have used makes it look really tacky once I filter down to one of the categories that has 5 items, because there is no scroll bar.
What I've tried using is:
Code: If Me.RecordsetClone.RecordCount < 10 Then Me.Scrollbars = 2 'vertical only Else Me.Scrollbars = 2 End If
But it still doesn't display correctly (nobar.png). This is probably my biggest headache right now, and every "solution" I've found, doesn't work.
I have a data entry form that updates a table - the table tracks workload inventories.
Beg Bal Inventory, plus Received, Minus Processed, equals the End Inventory
Users data enter these document counts every day, click a SAVE button and those counts are saved on the table.
The next day the user wants to open the form for that day's document count for data entry, and wants the End Inventory result from yesterday's record to appear automatically prepopulated in the beginning balance data entry field on the form.
I have a products form, we are a manufacturing company, with a listbox to show recent inventory transactions. This is based on a query which shows all transactions with the current part id, and that all works well and fine.
The problem is, I would like to limit this query to show only the last 10 transactions in the listbox and not make it editable, ie not enabled. I set the show only in query design view to 10 and it says in the sql statement select top 10, however, the listbox consistently shows all related records. What am I missing?
I'm receiving an error indicating there is a data type mismatch when running a query named qappInventoryTakeOn.
Data is entered into the Inventory Transaction Form. If the transaction type is "Take On", when the update button is clicked the record will be saved to tblInventoryMovements and then qappInventoryTakeOn should run to update tblInventory, but I keep running into the aforementioned error.
I have a Inventory DB and i want to integrate scanning. So far i have a table called StockInfo, this table holds information about bar codes (device type, make, model). I have a form called Scan_Barcode, on this form I want to be able to scan a bar code into a text box (text0) and use a afterupdate and requery function to display the results onto a form called EnterInventory.
Not only just the results but I want certain columns from the query to display in certain text boxes on this form. I have a query called FindProduct. that filters the criteria by whats entered on the Scan_Barcode text box (text0). So when i scan a certain bar code i can get the query to display certain product information for the bar code. I just cant get the results onto the HarwareStock form.
I am trying to run a simple update query to copy data from one column (Addrl1)to another column (Working_Addrl1) within the same file and I can't for the life of me figure it out. Then I need to repeat for addrl2 and addrl3 to working_addrl2 and working_addrl3.
hey, i have a form that keeps a record of my telephone enquieries at work..
i want to place a button on this form so that, whenever i have successfully dealt with a call, i can click this button and the record will go to a NEW "Logged Calls" form...
how can i achieve this? p.s i am very new to programming etc...
When user submits record on form, by pushing the arrow to move forward to new form, or back to previous ones, I would like to ensure that they have met a specific criteria in some fields.
How can I catch this without using VB Access? I can use VB Access, but would like to know how "normal" users would force form validation.
