I have set up a query to search for Customer Name by its first letter (thanks to Bob for that). I have created a form with a List Box which shows the results of this query.
What I want the form to do is change according to which result is clicked on from the list box.
My question is, how can I link the two so that it changes automatically.
btw.. i am not very good with VB so would appreciate a simple explanation
Ensure that the records displayed in the form are based on the results of the query. Then, add a Combo Box (yes I know you say List Box). As long as there is a primary key available then Access will give you three options when you add the Combo Box, one of which will be something along the lines of 'find/go to a record', use that. When you make a selection from the Combo Box you will find that the form automatically shows the record associated with it.
Now, here's the clever bit, in design view, right click on the Combo Box and choose the option to 'Change to... List Box'. (Although if there are likely to be several (say more than 5) results in the box I would suggest that a Combo Box is the better way to go from a GUI point of view. I also, for the Combo Box method, like to add a line of code that clears the value (sets it equal to "") from the Combo Box at the end of the method; but that is just personal preference because when someone navigates away from the chosen record the old choice is still visible in the Combo Box.
The good thing about doing it this way is that Access will give you all the VBA that you need, so you just need to cut and paste to try and get things working as you want them to.
Note, if there is no primary key associated with the records that the form has a the record source the code will not work as well, and you probably won't even have the option in the wizard when you go to add the Combo Box. Whether there is a primary key available at all will be down to your design of the table and query which gives you the results you currently have.
If you can't change the Record Source for the form to the query you are using the VBA that Access gives you should still be a big clue on how to progress, but watch out for records appearing in the Combo/List Box that do not appear in the search results.
I have been developing my database further and was wondering whether there is any way that the whole form can be refreshed so that another search can be done, without closing the form and re-opening it.
Tim very kindly and helpfully told me how to update a forms fields by using a combo box. This is based on a query that seraches for the first name of customers, the results of which now appear in a list box and further information fields and a subform below that.
I have added a 'Refresh' button using the 'Button Wizard' but I have found that this only seems to refresh the list box (i.e. asks me for another search) but the results will not refresh in the fields below the list box and in the subform.....?
It is puzzling me as to why this might be, when I know that they are all linked as when a search is performed that returns a number of customers, whichever one I click on, automatically changes the information in the fields below it, as it should. It just doesn't seem to do it when the form is refreshed.
Any ideas....? (I hope I explained it clearly enough..)
Because you say that you used the wizard to add the refresh button I guess that the code behind the button is a DoCmd. Personally if I ever create a button to refresh or requery something I would use
Code:
Me.[controlName.][Refresh|Requery]
Where controlName is optional (i.e: if you leave it out the whole form should be refreshed, or requeried) and you choose from using Refresh or Requery. The VBA editor (ALT+F11) will even let you know which options are available as you type in the code.
Try that out, experiment with the effects they have and let us know if that helps.
If you want to 'temporarily' prevent code from being used, but not delete it, put an apostrophe (single quote) at the beginning of the line. The code will then be coloured green and is treated as a comment, so not executed. Simply remove the apostrophe to reinstate the code.
Could you just confirm to me what controlName would be?
Is it like 'Combo 26'?
I have tried this, and it does appear to work, but it still doesn't update both parts of my form (the listbox and the related fields below)
Have you tried using both a Requery for the ListBox and a Refresh for the form, in that order?
Having gone back and read your 02:55 post (#4) I wonder if you need to do some other 'stuff' (nice technical term...). Is the task you are trying to achieve is to get back to a new list, having changed the selection in the combo box? Or is it to get back to the starting situation?
First situation: Is the list box being refreshed in the AfterUpdate event of the Combo.
Second situation: Would clearing the combo (me.comboName.value ="") and then going to the first record (me.move.firstrecord) (I think, this is off the top of my head)), do the trick?
Enough for now, it's far too early on a Sunday morning to be both up and actually thinking.
Okay, my fault. I presumed that you might be familar with the syntax of writing code that I was using.
In the example line of code that I gave:
Code:
Me.[controlName.][Refresh|Requery]
The square brackets around 'controlName.' indicate that controlName is optional. So you would use
Code:
me.refresh
to refesh the form or
Code:
me.controlName.refresh
to refresh the control specified by controlName (e.g: combo26).
I used the brackets around 'Refresh|Requery' and the vertical bar, to indicate that there was an option of choosing from one or the other, i.e: either Requery or Refresh.
Hope this clarifies things. Incidentally, if you name controls in Access and use spaces, Access puts in brackets around the name. So if the control was called 'my drop down' the above command to refresh would look like:
Code:
me.[my drop down].refresh
It is actually better not to use spaces, and to identify controls with short abbreviations. For example, you start all list box names with lst, combo box names with cbo, text box names with txt, I believe convention says that buttons should be cmd, but I prefer btn. So 'my drop down' would become, cboMyDropDown. The capitals make it easier to read, and Access doesn't need to but brackets around the identifier. Additionally, when you type in the VBA editor, controls without spaces in get listed in the prompt. For, for example, if you typed, 'me.' and then cbo Access would narrow down the list of suggested controls by going to the first item starting with cbo. The more letters you type in the narrower the list gets, it is then just a simple matter of either pressing tab or enter to complete the typing. This makes it so much quicker to write your code, and you can be sure that you have spelt the control name correctly as Access gave it to you.
Be careful when renaming some controls, especially the ones on combo boxes that you have created using the wizard, as they reference themselves in the code. If you do things in the wrong order you can end up loosing the code entirely and end up creating another control from scratch, sometimes they're better left alone.
You're welcome, and I just noticed a couple of typos in my reply (I used Refresh in the code but requery in my explanation, so I've corrected that now).
Another thing... sorry to keep asking questions....bt i'm learning so much!
I created the button as mentioned above, and everything worked perfectly. However, if I were to click on the 'New Search' button, but then decided I wanted to cancel the action, I click on 'Cancel' but it brings up a dialog box with 'Debug' on it, which is telling me there's an issue with the syntax. If I click 'End' it will proceed as normal, but it will be confusing for my user if they come accross this issue as they will not know what to do.
Like I say, the VB code and actions work perfectly, its just when I click 'Cancel' that it brings up this error....
If its working fine then why are you getting an error message. Is the error line in the Cancel code? can you post the VBA around the error line so we can help you
