Combobox question

[welbot]

HI,

I'm fairly new to access I guess. Done a little in the past, but a long time ago.
I'm trying to make a database for one of my workplaces, to help keep track of laptop parts.
We have a pile of laptops that we use as spare parts, and I want to record their brand, model, location on shelves, then a bunch of checkboxes to indicate things like whether the screen is smashed, if it has a battery and so forth.
I started with just one table with all the appropriate details just to keep it easy while I refreshed my memory about access.

So I have a form for data entry, and it works great. What I would like to know though, is can I use a combobox to select brand, and then only have models belonging to that brand show up in the next combobox, but also, if the model doesn't yet exist, be able to type it, and have it automatically associate with the brand selected for future use?
I'm guessing I might have to make a brand table, and a model table, then some sort of brand/model association ID, but will that allow what I want?

Also I'd like another form for finding laptops. Say I'm looking for a screen to replace, I enter the brand and or model, select a checkbox for the bit's I'm looking for, and have it either update the datasheet below in realtime, or press a button to show the results in a data sheet below. Also, can I then uncheck the screen option in that data sheet, and it will save it to the main table?

Using Access 2013 by the way!

Much appreciate any help

 

[Mihail]

If you will design a relational DB the things will be a lot simplified.
From your brief presentation I'm pretty sure that you need more related tables.
Unlike Excel where you keep the whole information in a single sheet, Access use multiple tables in order to store this information.
Each table is a logic group regarding the business.

Upload your DB (2003 or 2007) and hope I'll be able to show you "how to".
ZIP the DB before to upload.

 

[welbot]

Thanks for your reply. I hope this doesn't sound rude or arrogant, but I'd like to learn how to make it myself. I'm well versed in computer usage, and have done coding in the past, so I'm really looking for pointers on what steps I should take, rather than exactly how to do it. (I'm more likely to remember this way ) I'm not afraid to dive in and look up how to code something if I need to, I just more want to know what is the best way to go about it.

Anyway, while I waited for your reply, I re-organized things a bit.

now I've got 3 tables, and soon to add a fourth.

The three I have so far are tbl_Brands, tbl_Models, and tbl_BrandModelLink

I've created relationships between them all, so tbl_BrandModelLink will auto generate a BrandModelLinkID, which I guess I will then use to link to the fourth table tbl_Laptops, where the details of each laptop will reside, and as such, each laptop will get an auto generated LaptopID.

Sound right so far?

I'm guessing I need to need to create a form with a combobox for brand, and then based on what's selected, pass the BrandID to the model combobox, so it only displays records with matching BrandID's? Do I have to do this with SQL, or is there a property of the combobox I need to change?
I figure once I've got that, I would then need to feed the BrandModelLinkID to the tbl_Laptops?
What's the easiest/best way to achieve this?
Once the link between the combo boxes is setup, if an entry doesn't exist, and I type in a new brand or model, will a new entry in the appropriate tables be created, or is there more trickery involved for that to happen?

I'm happy to upload a copy of the database if you can't picture it clearly enough, but I would prefer not to have it just done for me

Thanks again for the help!

 

[Mihail]

I hope this doesn't sound rude or arrogant, but I'd like to learn how to make it myself. I'm well versed in computer usage, and have done coding in the past...

This is not a problem of arrogance.
There are only 2 years from when I switch to Access and databases after years of coding in VBA for Excel or in VB. (5 and 6). This background was allowed me to understand quickly the new environment and how to design a DB. And my learning was the best when someone has showed me my errors and the good way. It is why I ask for your DB.
Sorry but I can't help you while I don't understand your business (and by seeing your DB is the faster way).
If I make a new design for your actual DB you can see the differences, understand on your own why or ask me why. I think that this is the fastest learning curve.
For the beginning Google (a book is a lot better), find, read, and understand very well the concept of normalization for databases. You will never be able to design a DB without this understanding.

From now is your choice.

Good luck !

 

[welbot]

Thanks for the reply AccessBlaster.
I did a little re-designing last night and after thinking about implementing the fourth table, I decided against it, so now I have a tbl_brands, tbl_models and tbl_laptops.

I managed to get the combo boxes to filter as well (so you pick a brand, and only models that belong to that brand show up,) but it seems to be having an issue moving on from there. Perhaps I have the wrong afterupdate statement in the cboModel box?

I think it's adding entries to the tbl_brands when I pick one and then try to select a model. It makes a ding noise, and then when I look in the brands table, there's a number in there (I'm guessing it might be trying to write the modelID from tbl_models to the tbl_brands.)

I'm going to play with it a bit more tonight cos I feel like I'm really close, but the one thing I would like to know at this point, is how can I set it so when the blank form opens up (as it does now, all inputs are empty), and have it not write anything until I hit a button? Is that just the dataEntry property of the form, or do I need to tell each input not to write to tables until I'm ready?
Or do I need to change the combobox to just do a lookup somehow, and then use the NotInList function I was reading about, to add new entries to the list?

I will be the primary user of this database, but there will be another fairly regular user as well. I'm not worried about him so much, but a third part timer MAY use it at some stage, and he's dumb as dog shit, so I need it to be dead simple. He's messed up enough stuff already
Hence my want for separate forms for entry and searching.

I would like to simplify the options to something like a simple condition statement, but the whole point of this is to know what laptop has a part that appears to be usable. Even though a screen may not be cracked, it may have been written off because it's just plain dead. I just have to try and find a part, then test it to see if it works. If it doesn't work, it goes in the bin!

I've basically got a pile of like 500 laptops, and they're randomly distributed on some shelves (and the floor and all over the place.)
I can't dedicate my time to adding entries for all the laptops, as I have higher priorities, so I need something quick (as in time taken to enter data), so when I or another tech goes to find a part, they'll bring a laptop back, enter in the details (like whether it has a dvd rom, non cracked screen, keyboard, whether the keyboard is missing keys, and location information, Shelf letter/number for the laptop, and whether the keyboard has been removed and put in to a box.
This is why I thought checkboxes might be easier, as each laptop may not have all the bits.

At the moment I can spend 30 mins looking through laptops only to find we don't have the bit we need, so I'd like to know I could just search for eg. Acer - 5750G and then check the box for the part I'm looking for, and it'll show me what shelf it's on if we have any.
There's probably about 5-8 laptops on any shelf too, and they're also overflowing, so I'll just use text entry for location, as it could change (might be floor, or on top of something, so no need to have combo box or anything for that.

With what I did last night in terms of table design, does it sound like I'm on the right track for what I want, or do you think I'd still be better to just have a lookup in the form, and then maybe a new form for adding new brands/models?

No doubt I'll go mad in a matter of hours, specially having been at work all day, so I'll probably come back and upload the database for you to peruse.

Much appreciate the input

 

[Mihail]

Now, I think that is the time to upload your DB in order to take a closer look.
Convert it, if necessary, in Access 2003 or 2007 version then upload.
From what I understand, you need a tool that can tell you where you find a certain device, isn't it ?

 

[welbot]

That's right. It's basically to help me organize everything
I think I figured out why the form wasn't doing what I expected too. It seems that the properties section on the side wasn't updating when I selected different objects in the form, so I must have put a rowsource statement in over the top of another.
I seem to be going ok for the moment, but I just read something that made me wonder if I've taken it too far. Can I use a listbox, and still be able to add new models etc? I thought a combobox had to be used to accept input, but now I'm not so sure anymore?!

 

[welbot]

I zipped up the database in 2k3 format, but it looks like it destroyed my form. After the conversion it only has the tables for some reason. Still, if it helps it helps.

http://users.tpg.com.au/welbot/DNF_2k3.zip

 

[Mihail]

Is this what you are looking for ?

 

[welbot]

Hi,

Thanks for your pointers. I actually ended up getting my original plan working. Your links lead me on to some interesting reading, which revealed to me, the fact that columns start at 0 (which I already knew), but that bound columns start with 1! This was causing me a lot of confusion I think.

So I now have it setup with a table for brands, models, and laptops.

Pick a brand, it selects model based on brandID, then enters the modelID in to the laptop table with my other data. I then press a button to Save the record, move to a new record, and the comboboxes reset back to blank values!

Now I just have to figure out how best to go about accepting new entries in to the brand or model combo box.

Is there and easy way to feed the value of a combobox when a notinlist even is triggered? Or do I have to make another form pop up to take care of that?

 

[welbot]

Thanks for the sample Mihail. Although it's not exactly what I was looking for, your example did give me some ideas on implementing the search side of things That will come in very handy!

I'd upload what I've done with mine so far, but everytime I try to save it as a 2k3 file, it just deletes my form, so it's kind of pointless :\ Which is a shame, cos I'm kinda chuffed with what I managed to do so far!

 

[Mihail]

Thanks for the sample Mihail. Although it's not exactly what I was looking for, your example did give me some ideas on implementing the search side of things That will come in very handy!

I'd upload what I've done with mine so far, but everytime I try to save it as a 2k3 file, it just deletes my form, so it's kind of pointless :\ Which is a shame, cos I'm kinda chuffed with what I managed to do so far!

No. The form is there, blue (if I remember well) and with a lot of (unnecessary - forgive me, please ) check boxes.
Take a closer look to Navigation Pane after saved in 2003 and you will see it

Glad to help you