Recordsets

Hello, everyone. I have what could be an easy problem, but I have no experience with Access, VBA, or SQL at all. This is a long post and I really appreciate any help I can get on this.

I am trying to do an inventory system. I need to tie in ethernet jack numbers in with asset tag numbers if the equipment we have.

I have a few tables set up, but the one I am concerned about is my tblDataPorts (which includes all the data ports I need to be concerned about). The format of the records in this table is "building number"-"room number"-x-x-x. I am only concerned about the building and room number portions for these records.

I plan on having this all local on a laptop. I will carry the laptop around with me with a bar code reader attached. As I get to a different room, I will select building number and room number from a list (store them into strings- strBldg and strRm). What I am trying to do is have a listbox populate with only the port numbers that match up with the building number and room number.

I have a query that will do this, but it does not accept the values from the strBldg or strRm. Instead, I have to physically execute the query and type in the room and building numbers (which is what I am trying to avoid).

What is the best way to go about this? I have tried many, many different way. From my understanding, recordsets would be the best way to do this, but I am not sure how to do it. I will not be connecting to any external database and everything will be saved locally on the laptop.

Any help that can be provided, again, will be very much appreciated. Please let me know if more information is needed.

Thanks.

Sam

Welcome to the forum, Sam. I don't think you need to be concerned with recordsets. If I understand you correctly, all you want to do is populate one listbox with port numbers that are determined by the bldg. number and room number. All that's needed is a query that does that, as the Control Source for the listbox. What you haven't told us is how the data is structured. You mentioned the tblDataPorts and showed fields for Bldg.# and Room#, but not port numbers. Is that another field in that table? Is it possible for there to be more than one port number for a given Bldg.# and Room#? That's the sort of information needed to explain how to populate your listbox.

Thanks for the reply! I'll explain more about what I have set up.

I have a table (tblDataPorts) that has 2 fields. ID (primary key) and PortNumbers (this field has the *-*-*-*-* format).

I have another table (tblLocation) that has 4 fields. ID (primary key), Abr (abbreviation for the current building), Num (building number), and Rm (room number). There are multiple rooms for the same building, obviously.

The other tables, I dont think that I'll need. They include the table for me to input the inventory and another one to input everything (equipment with data port) into one table.

I have a query (qry_PARAM_SELECT_Port) with the following SQL:
SELECT tblDataPorts.PortNumber
FROM tblDataPorts
WHERE (((Left([PortNumber],3))=[Enter building: ]) AND ((Mid([PortNumber],5,3))=[Enter Room: ]));


I have a form, frmDataEntry that has a few boxes and buttons. I believe I am more concerned with the combo box (cmbBldg) and text box (txtRm). When I click the button (btnSubmit), I need the values in cmbBldg and txtRm to be sent to qry_PARAM_SELECT_Port (or just have it so that the query executes with those values. If I have to code the query into VBA, then so be it. You are all the experts ).

cmbBuilding is pulling its contents from tblLocation (it is excluding the Rm field). Its bound column deals with the building number.

After qry_PARAM_SELECT_Port runs, I need the results to be put into a list box (lstPorts). What I am aiming for is that when you change your building/room location, the lstPorts will change to reflect the ports that are assigned to that room (again, the format of the port numbers is building number-room number-x-x-x. I am only concerned with the first two numbers.) The query mentioned above matches the output from cmbBldg and txtRm to match the format of the data ports.

I hope this is the information you were looking for! Everything I am working with is all text. Sorry to confuse you about the tables. Again, any help would be greatly appreciated. I am so out of my element here, but was assigned this task to complete.

Thanks again!

Ahh! The key element I was missing is that the "port numbers" are really a combination of the Bldg#, Room# and other numbers. So is your query working properly when you enter the values manually? If so, then your only remaining issue is how to get it to pick the values from the form? That's an easy one! Here's how you do it:

Where your query now says [Enter building: ] and [Enter Room: ], you need to replace those expressions (which cause the parameter window to pop up) with references to the controls on your Form that contain those values. The syntax looks like this:
If you were putting the SQL into a string, using VBA, the syntax would be just a trifle more complex, but if you are using just a regular query, as I think you are, all you need to do is substitute the syntax in blue, above, in the Criteria row of your Query.

If I understood you correctly, that should do it for you. If not, come back and ask again.

Amazing, that should work for me.

Another question: How can I have my btnSubmit run the query and then have the lstPorts listbox update so that it shows the new ports for the new room?

I think I have the updating the listbox as lstPorts.Requery, but making the btnSubmit execute that query is lost on me.

If I code it in with VBA, will I run into an issue with the SELECT query? I can never execute a SELECT query. I get an error when I do so. That is why I'd like to execute the qry_PARAM_SELECT_Ports the way it is and not have to work on writing a string query for that.

I'm sure its simple, but again, this is the first time I have ever touched this stuff. Thanks so much, so far, you have been a great help!

That's pretty easy, too. When you know how! Every object in a form (as well as the form itself) has a bunch of Properties, some of which are Events. When you open the Properties window (from the View menu or the Toolbar or by right-clicking on the object), there will be a tab for Events. No doubt your btnSubmit has an Event Procedure showing for its On Click event. By clicking on the ellipsis (the 3 dots ...) to the right of where it says "Event procedure", the VBA editor window will open with that procedure showing. It probably now has a line of code that executes your query. You just need to add a line below that to requery your control. You don't quite have the right interpretation of "lstPorts.Requery", that's not a query, it's the command that tells Access to requery the listbox lstPorts, using whatever query is set as its Control Source. The syntax should be something like Me!lstPorts.requery (the "Me" stands for "the form in which this code is running").

I'm not sure I am visualizing your form accurately. When you click the command button, what is updating, just the listbox, or a subform, or the whole form, or what?

Again, stellar information!

When I click the button, I just want the values from my cmbBldg and txtRm to be passed to qry_PARAM_SELECT_Port--who's results will be displayed in lstPorts. All of this is on the same form--I'm trying to keep it simple

When I get back into the office, I can test this all out. Thank you so much for your help so far. Its really appreciated. I may have some more questions, but this is the biggest issue so far

If there is something else I need to know with the information I provided, that'd be awesome. Otherwise, I'll keep you posted as to the progress of this situation.

Upon working with the suggestions you made, I have a few snags.

I put the [Forms]![frmDataEntry]![txtRoom] and [Forms]![frmDataEntry]![cmbBldg] into the correct locations and for some reason the query is not picking up the values. I tested with a message box to make sure that the value that the boxes are displaying are what I want and they are. Do I need to add a ![Value] at the end or something? I tried that and it didnt work, but the syntax may be messed up.

Also, when trying to match up the qry_PARAM_SELECT_Port to the btnSubmit, I get an error. I cleared all the VBA code out of the button and when I click on the elipse, I go to expression builder and select the query. Is there anything else I need to do with that? The error I am getting says:

"The expression On Click you entered as the event property setting produced the following error: The objects doesn't contain the Automation object 'qry_PARAM_SELECT_Port"."

There must be something I am missing, obviously. I've tried different types of procedures and whatnot to get the button to run the query, but no luck. Also, the data I need just wont pass through. :\

OK, let's try to get the concepts straightened out, first. I think you're thinking in terms of "pushing" the data around, when in fact, it's more like "pulling" the data as needed.

You have a listbox on your form. The data that appears in the listbox is determined by its Row Source*, which is probably a query in your case, either a named (stored) query or a SQL statement in the Row Source property. When you first open the form, you may or may not want something to appear in the listbox, but once a Bldg# and Room# have been selected, you want the appropriate DataPort#s to populate the listbox. By the way, are the Bldg# and Room# also listboxes? You currently have a command button to click, although you wouldn't necessarily have to do that, you could just have the listbox requery every time a value was changed in either the Bldg# or Room# textboxes. What you need to have happen is to requery the lstDataPorts control to update its list. You don't "send" any data (in an active sense) to the query, it "pulls" the data it needs from the other 2 controls, as it executes. It does that because of the Criteria expressions for the 2 fields. The syntax is exactly what I showed you before. The default is to the "value" property, although it will also work if you express it as [Forms]![frmDataEntry]![txtRoom].value -- it's just unnecessary.

Tell you what I'll do: I'll make a little sample mdb and attach it here, to show you how it works. Give me a half hour or so.

* (I see that I made an error in my first reply to you, where I said "Control Source", which is incorrect. The Control Source property determines which field, if any, of the forms' Record Source property supplies the current value for the control, and which will be updated by any changes made to the control. The Row Source property determines where the selections come from, which could be a table, a query, or just a list of values. Sorry for the confusion.)

OK, Sam. Take a look at this. I think it has the functionality you're looking for. You should be able to follow through on how this is done, but if you get stuck, post here again.

Oh, and for this demo, I didn't bind the control source of the DataPorts listbox to any table, but you will likely want to do that, depending on what you're going to do with the data.

This looks amazing. When I'm in the office tomorrow, I'll take a long look at this and what I have going on and figure out where I went wrong. You are a life saver!

Nothing up my sleeves, no smoke and mirrors, I promise! Any child of 12 could do the same thing--with 20 years experience!

I finally got it working! There is a small issue with the qryDataPorts, because the room numbers are 1 to 4 digits long, but this I have it working exactly the way I wanted.

Thank you very much, once again. This is such a huge relief to get done. As I said, I deal more with network issues, Active Directory issues, and 1st and 2nd level user support. The whole development side is a complete loss on me. I am glad there are forums out there to get help from

Thanks for answering what were extremely simple questions for you. I really appreciate it!