I have developed a small database (attached) but want to jump to the recorde selected. Anyone got any idea how I can achieve this - it seems to be 1/2 working, but I cannot figure out the rest.
I presume what you mean is that you want to have a drop-down box for the Tutor's Surname? You can do the majority of the work required by using the built in Combobox wizard:
Add a new combo box to your form, the wizard should then give you three options, one of which is "Find a record on my form based on the value I selected in my combo box". Select this option. Then the appropriate fields, I would suggest both Surname and Forename as you have some records without surnames and if you get two people with the same surname it will at least give the operator a chance of getting the right one; you can adjust the column widths so that only the first couple of characters show anyway.
Once you have completed the wizard you'll probably want to reformat it the same as the other controls, go ahead, but do not change the name of the combobox unless you also change the code reference to it, otherwise the code generated by the wizard will not function.
Um....what if you only have two choices? What code am I looking for that is generated by option #3? The two options that I have are:
"I want the combo box to look up values in a table or query"
"I will type in the values that I want"
Like I said, I don't have the third option but that's exactly what I want to do - look up record on a form by selecting an option from a dropdown list.
Could someone post the code in their combo box? Thanks!
I *think* that this happens when Access can't determine a key/ID field in the recordset that the form is bound to.
If you want to see the code you can do so by creating a table, give it at least two fields and make one a key field. Put some a few records data in. Create an autoform based on the table then add a new combo box, and hopefully this time you will get the third option. Once the combo box is there, view its properties and check out what Access has created (yep, it's all done for you).
It's worthwhile examining the code and taking time to understand how it works because it may help you to fault find some time in the future, also you might then be able to see how you can adapt the code to be used in the form you are working on.
Try not to use spaces in the field names in the table. It can create all sorts of problems - to see how follow the instructions here, then put spaces in the field name and follow all these instructions again, then take time to compare the code generated for the two different combo boxes. Once you have done that try and type in the code fresh in both cases to see how Access can help you when there are no spaces.
I'm not trying to cop out putting a code sample on here, that would be quite easy (and quicker than writing all this), but I think that you can learn a lot from carrying out processes.
A word of warning, if you change the name of the control (the combo box in this case) the code will effectively 'disappear'. If you really don't like the name Access gives the control, change (ALL) the references to the 'new' control name in the code first, then change the control name and finally set the event property back to the code.
Tim, I've done this before, I just can't seem to locate my code that does this. I have been working as a SQL DBA and I created a small access database to manage some server updates that I am making - quite a laundry list of changes that I am making - and I want to quickly be able to referece the objects that I have input into my access database.
I have been programming for nearly 6 years so forgive me if I'm not completely thrilled that you didn't put up a code sample and chose instead to try to teach me the process. I'm just trying to get some work done and I don't need any enlightening instruction.
Nevermind - I had my form based on a query so that the records were returned in a specific order - I changed the form data source to be that of just the table and now I have the third option in my dialog box.
Sorry that you feel let down, although I must say that if things were really that much of a rush I would have expected you to have responded sooner to the previous post. My apologies for only responding to your latest post now, I don't have an Internet connection at work and only got the notification that you had made a post when I got home.
I am glad that you have solved the 'missing third option' problem. I think that this proves that the problem was something to do with the query. I know for a fact that it is possible for the third option to be displayed when the form RecordSource is a query; it appears for one of my forms which is based on a query.
Okay, I've just done a little experiment. The RecordSource seems to need a key field, or at least one that is Indexed (without or without duplicates), otherwise the 'third option' isn't available when placing the combo box.
The code that is produced is as follows.
Code:
Private Sub [B]Combo01[/B]_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[[B]FieldName[/B]] = " & Str(Nz(Me![[B]Combo01[/B]], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
I've highlighted the bits that you'd change if you chose to add the code yourself to one of your own combo boxes. You need to bear in mind though that the first field (column) for the combo RecordSource is the one that is used for the bookmark comparison, although this does not have to be visible. I'm not sure what would happen if you tried to use it for a form where the RecordSource doesn't have an indexed field, although I could surmise that it would only take you to the first record that matches the value chosen in the combo box, if there were multiple entries with the same value that is; this may or may not be a problem, depending on your application.
I think that it should be possible to easily reinstate the sort that you wished to have (but have, hopefully, only temporarily lost). The easiest way of doing this might be to click on the Form properties RecordSource then the ... button, Access, the 2003 version at least, then presents a dialogue box:
"You envoked the query builder on a table
Do you want to create a query based on the table"
the query that you build, unless you remove some fields, should be fully compatible with your form and it should be a piece of cake to set the order as you previously desired.
Hey, no problem. It wasn't an urgent issue. Actually, my issue here really isn't Access - I just figured that if I could add that little feature to this small app that I created to track these changes, it would make things easier.
Right now I'm in the process of making a beta environment match production and we have a LOAD, let me emphasize that again, A LOAD of things that need to be updated - I have 73 jobs on just one server alone and the beta environment encompasses 3 servers and too many databases and DTS Packages to count. While the databases themselves can be brought up to date by restoring backups of the current production environment onto the beta environment, we want to go through these jobs bit by bit in an effort to rid ourselves of unecessary code, and make sure step by step that the server, DTS and file refereces are correct.
At the moment I'm updating the jobs to reflect current production code, and I have to go through all of the objects that the either depend on the job, or the job is dependent upon to make sure that all paths, objects, files, server references, etc, are updated. A daunting task to be sure, and one that is going to take more than just one or two days.
Thanks for posting the code. After I created the dropdown list, I changed the record source for the form back to a query and it still works like a champ.
