querying fields and records or building event

[iWannaNerdOut]

Hello everyone,

I'm new to Access and it seems to be really fun so far but I need help with the main part of this db. I created a table that has 3 fields (columns). They're partnumbers, modelnumbers, serialnumbers. I would like to be able to click on the model number dropdown box in one of the forms and be able to see the available serial number that matches either the part number or model number. I can't seem to find anything out in the web or youtube on how to do that. I'm gonna guess that I need to create a query or is it a macro, or building an event? Any direction will be awesome and appreciated.


Regards,
Tony

[Bob Fitz]

Hello everyone,

I'm new to Access and it seems to be really fun so far but I need help with the main part of this db. I created a table that has 3 fields (columns). They're partnumbers, modelnumbers, serialnumbers. I would like to be able to click on the model number dropdown box in one of the forms and be able to see the available serial number that matches either the part number or model number. I can't seem to find anything out in the web or youtube on how to do that. I'm gonna guess that I need to create a query or is it a macro, or building an event? Any direction will be awesome and appreciated.


Regards,
Tony

Use the wizard to create the combo box on your form and it will offer you the choice of which columns you want to see when it is dropped down

[Bob Fitz]

This link may be of help: https://www.youtube.com/watch?v=5kxgvMW0FoU

[iWannaNerdOut]

Thank you Bob. I'll give these a try.

[Bob Fitz]

Thank you Bob. I'll give these a try.

You're welcome. Post back if you have any further questions

[iWannaNerdOut]

thank you Bob. what I wanted to do extra is when someone clicks on the combo box to choose a specific model, I'd like the cooresponding serial numbers with those models pop up a separate window or probably easier is to show up in the Serial Number box.

When I right click on the Serial Number box in design view, should I create a macro builder, expression builder or Code builder? Gosh, I hope I"m explaining it ok. Thank you in advance.

[Bob Fitz]

thank you Bob. what I wanted to do extra is when someone clicks on the combo box to choose a specific model, I'd like the cooresponding serial numbers with those models pop up a separate window or probably easier is to show up in the Serial Number box.

When I right click on the Serial Number box in design view, should I create a macro builder, expression builder or Code builder? Gosh, I hope I"m explaining it ok. Thank you in advance.

I assume that you have created the combo box now with three columns.
Create a textbox on your form and set its record source to
= [NameOfComboBox].Column(1)
Which will show the value in the SECOND column. Column[2] would show the value of the third column.

[Bob Fitz]

I've attached a very simple example:

[iWannaNerdOut]

Thank you Bob. Your suggestion helped me out. But how can I designate a field (aka: cell in a column) a specific value? For example, if the designation for that field is that it's "unavailable". I want a results button or query to return that value, "unavailable" or "checked out".
What I want to create is a blank search box that will query any serial numbers under the serial number column and return a value such as its 'checked out'.
I also want to designate a serial number to a model number that's in the other column, in the same table.

Thank you so much in advance.

[Bob Fitz]

I'm unclear about your requirements.

......But how can I designate a field (aka: cell in a column) a specific value? For example, if the designation for that field is that it's "unavailable". I want a results button or query to return that value, "unavailable" or "checked out".

Where is this data to be found.

What I want to create is a blank search box that will query any serial numbers under the serial number column and return a value such as its 'checked out'.

But that's what the combo box does if the value 'checked out' is in the fields of the query used in the Row Source property of the combo.

I also want to designate a serial number to a model number that's in the other column, in the same table.

And that's what the combo box does. In the example given it shows the value of the first column in the combo and the value of the other two columns in the textboxes.

[iWannaNerdOut]

thanks Bob. The data is in one table.

for example:

SerialAndModelNumber table

ModelNo. SerialNo.
Model 1 ABCDE123
Model 1 FGHI456
Model 2 ZYXW321
Model 3 RSTU654


I would like to have a combo box with the Model No. for my team to choose from and a Pop up or dialog box for them to select the serial numbers associated with that model number. Once they select the serial number for that laptop model, I'd like the serial number to be labelled as unavailable so anyone else looking for the same model won't accidentally pick the same machine. Sorry for the confusion and thank you for your time.

PS: Someone suggested maybe an If, Then, Else statement? I know the expression but I've never had to create anything with them.

[Bob Fitz]

Try the attached db.

Use the 'Available' combo to find a record. Once Part Number is filled in that record disappears from combo.

[iWannaNerdOut]

Thank you Bob. Would you also recommend and If Then Else statement to label a serial number in the serial number table as 'checked-out'?

[CarlettoFed]

In the attached file you can see an example of how to manage it.
In the initial form, where you have a summary of all the models with the various serial numbers, you can carry out the various searches that you can cancel with the appropriate button.
In the form frmAssignment you can select the Model and assign it the Serial Number, if it is a new record in the Model and Serial Number only those that are not currently assigned will appear.
Always in the frmAssignment form, when you are in the Model or SerialNumber combo:
- if you write a value that does not exist in the relative table, it will ask you if you want to insert it
- if you have selected a value by double clicking on it, it will open the relative form to allow the modification

Sorry for my possible incorrect English being Italian.