Dynamic form: inventory system

[The_Dutchman]

Hello everybody, my first post here !

Access is probably the hardest program for me of the office packet, so please be a little patient .

Troughout the years I managed to collect a stock of electronic components which I cannot oversee no more. Now I am making an inventory system with the Name, Group, Package, Datasheet, Prize, Count, "Where to buy".. and so on. The right table is already created. I guess that was the easy part.

Now I am trying to make a form that does the following things:

I Want to use combo boxes to select the category, then the name, then the Package where it is in. These can all vary. When all is selected I must be able to change the description or the count. Then I want the record to be updated. However when I change 1 of the combo boxes (add a new component to a specific category) a record should be add.

I am trying and searching for over 2 days now. Can't get anything done right. I just don't know where to start. Please help me out here.

Greets,
The Dutchman

 

[The_Dutchman]

My first post here and no response over 2 days.. quite disappointing
Is my post layout a little bit wrong or something else ?

 

[Scooterbug]

Creating an Inventory Database can be a bit...daunting. Basically, the ideal way to set it up is to have a table that records all the transactions (parts coming in and parts going out). You can then use a query to add up all the incoming parts and subtract all the outgoing parts. This should give you a total of what you have in stock.

From what you described, I am hoping that you have multiple tables and not just one. Based on your description, you are going to need at least 2 tables. One to store the product information and one to record the transactions.

 

[HAMMAMABUARQOUB]

Heloo,,, nice and important question... as i understood.. and with experince in creating inventory systems using Access over 5 years,, i can say..Northwind DB supplied with ms access can be the right and yet the easiest way to start learning everything about access, you can also use the assets and household inventory templates to learn more,,,

 

[missinglinq]

My first post here and no response over 2 days.. quite disappointing
Is my post layout a little bit wrong or something else ?

You have to understand that this and other Access forums are designed to help posters with particular technical problems that they encounter with Access, i.e. being unable to sort data correctly or how to parse data properly or how to get data to display in a certain way. etc.

The purpose here is not to show a poster how to design a database, for a particular application, from beginning to end, essentially doing all the work for them. For this you would need to go to an Access consultant or development firm and pay the attendant fees. When such a thread is posted, essentially asking, "How do I create a database?" most of the developers who donate their time and expertise here will simply ignore it, as you've found out.

As HAMMAMABUARQOUB has said, the Northwind database, supplied with all versions of Access, is a great place to start the learning process, and you are going to have to spend some time learning Access in order to develop a database such as this. After learning the basics, you can search online for templates using "MS Access" "templates" and "inventory."

MS Access, unlike many of the other components of MS Office, and regardless of the Microsoft hype about "anyone can develop a database with Access", does have a rather large learning curve, and the bottom line is that you're not going to simply open it up and start writing complex databases in the same manner as you would open up MS Word and start writing a business letter.

After you've spent some time learning the basics, and start developing your database, come back to us when you have specific questions about specific problems and I'm sure you will find the people here more than happy to help you. That's why we're here.

 

[The_Dutchman]

I want to sort something out. Maybe I've told a very big story in my start post but I do have a specific question. I'll come back on it later.

I'm active on a lot of other forms and I also did not expect to get a "good to go" solution. Just a hint or a piece of code.

First of all it is an inventory system which only stores what I have in stock. I don't want to record the transactions. I Just need it so I would what to re-order in time. If the count goes under 2 e.g. (I could run a query for it to search for these records)

I have experience with VB, C, MySQL and PHP for web design etc.. But I've never went to Access. So making up the right table was pretty straight forward (opening it up for the first time ).

I Could add my database to the project, the only thing that kept me from posting the database is that it is completly in dutch. But I'll post it so you see I am really working on it.

So with al this information I think it would be sufficient to just work with one table wich I can update everytime with the same form. And thats where my specific question is.

I want to use comboboxes. By selecting the group category and name and package a component is fully defined (it can't be any other). If I use SQL I can get ALL those values in the combo. But I want it sorted out. If I select a group I want to only display the Names of the components WITHIN that group. So with the Name combo selected I want to select only the Packages that belong to that Name. Then the component is defined and the count, description, attachment will appear. If I now edit the count box that specific component( ComponentID ) will be updated.

BUT !! If I enter a new value in the package combo box , with a fitting description, attachment and count I want it to add a NEW record to the database with a new componentID.

Can you see the trouble? This is so much information that I don't know just where to begin with. I think I must start with trying to sort my data out on the form it gets selected. I've tried this alot already but keep getting empty combo boxes. I think the componentID is messing it up. I will add an attachment of a sample database wich doesn't contain my "already good" form but just something for testing. The filtering works almost. I can't get the count to appear. Any comment is welcome.

Already thanks for the replies and understanding.

 

[HAMMAMABUARQOUB]

1-filtering comboboxex based on each other is not a tough, yet not simple thing,,, you should be able to write the SQL statemnet to filter them...as you say you have experience with VB, C, MySQL and PHP for web design etc that's a very great thing so you need your knowledge in SQL to write down the statements, but also you need VBA code with the right argunments as field names in specific forms!
For example: you have comboboxes 1 2 and 3
the code will filter the combo 2 data using "rowsource" while the Where condition in the select statemnt should be:

"Where CategoryID= Forms![Form1]![CatID]"

and so n for the 3rd but remeber first selction

"Where PackID= Forms![Form1]![PackID]"

Now,,, adding new records...
I used this code for "No-in the list " but modified to deal with non- in the list as new values,, so these new values go directly to the table, as you suggest these values cannot be directly used in the form.. else the form needs refresh..the solution i suggest is when new data is entered...write a code to insert it in the related FK fields in the subtables,,,,
ex: Newcategory added in combo1,,, creat SQL insert to add this Id to the Packs table in the FK (CatId)
that's all
i will try to fix some codes and post them here,,,
HAMMAM

 

[Scooterbug]

First of all it is an inventory system which only stores what I have in stock. I don't want to record the transactions. I Just need it so I would what to re-order in time. If the count goes under 2 e.g. (I could run a query for it to search for these records)

If you dont use some kind of transaction history, how are you going to know what you have used? If you want it to figure out your current stock, you need to record when items leave your stock. You also need to record when you replenish your stock. This is what I meant by transactions. If you simply want to manually change the total everytime you take something out or put something in, you could simply use an excel document.

 

[The_Dutchman]

@HAMMAMABUARQOUB:

I figured the filtering with comboboxes out.

SELECT DISTINCT Components.CompName FROM Components WHERE Components.CompGroup = Forms!CompForm!cboGroup

But refreshing is indeed annoying. I din not yet find a way to put the Count in the textbox like you see in my attachment. I was trying to do DLookup but I still got a syntax error. I think it must be done in VBA. Will try this later.
Example code would be awesome !
I think it is a must that I put a hidden box on the form with the ID.

@Scooterbug
I Think you misunderstood my second post. I will use the same form for recording new items as for updating the count values when I buy-in or use.

Thanks in advance

 

[Scooterbug]

If you are going to manually change the amount based on when you use it and when you purchase more, then all you will need to do is to put a field in the table that has the products for quantity. Once you filter out by catagory and/or Package, you could use that criteria to display the relevant data into a subform. Include the Quantity field and you can update it from there.