473,320 Members | 1,947 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

List Box Go To Record

Hi everyone,

I have a database with a form on which I have a listbox which is used as a kind of 'menu' (i.e. it lists the titles of all the records in the database). Can anyone help me set up a code so that if I double-click on a record title in the listbox, the form will jump to the corresponding record? I've tried various DoCmd.GoToRecord codes, but I am definitely doing something wrong, because it never works.

In other words, the code has to recognize the record title I'm clicking in the listbox and go to the record with the corresponding title.

Thanks for your help in advance!
Jul 5 '07 #1
10 36346
Scott Price
1,384 Expert 1GB
Are you trying to go to the selected record on a form? And are your forms based on a query or table?

A couple of general suggestions come to mind:

1. change the list box to combo box (in form design view right-click and choose change to).
2. In the properties dialog, make sure the bound source is set to the field that includes your RecordID.
3. Use this kind of code: DoCmd.OpenForm "[2ndformname]", acNormal,,"RecordID = " & Me![comboboxname].Value

If you are instead trying to look up the record in a table, I would suggest that in the long run you'll find it easier to create a query based on the table, and form based on the query. The form can always be viewed in Datasheet mode if you prefer that type of view.

You likely know all this already! But hope the suggestions help...
Jul 5 '07 #2
Scott Price
1,384 Expert 1GB
For clarity the DoCmd.OpenForm code line will go under the After Update event of your combo box.
Jul 6 '07 #3
Thanks for your reply, Scott. I tried your code, but it actually does something else (it changes the RecordID in the record I'm viewing to the ID selected in the combobox). Let me give you some more detail:

I have a listbox in the "form header" of my form that lists the titles of all records in the underlying table. The form itself is based on a table.

I would like to be able to click on a title in the listbox at the top and have the form jump to the corresponding record.

I can definitely change the listbox to a combobox, if necessary.

Thank you very much for your help! It is much appreciated!
Jul 6 '07 #4
Scott Price
1,384 Expert 1GB
Let me see if I understand this right: In your form header you have the list box with record names. In the main body of your form you have the records?

The code I gave was to open a separate form with the record details on it.

May be personal preference, but one reason to change the list box to a combo box is that a combo box is more flexible when you have more records to display. List boxes get rather unwieldy when showing more than a few lines of records.
Jul 6 '07 #5
Hi Scott,

Yes, you've got it right. The listbox is int he form header and the main body of the form has the records. I agree that combo boxes are more flexible, so I will likely switch the listbox to the combo box.



Let me see if I understand this right: In your form header you have the list box with record names. In the main body of your form you have the records?

The code I gave was to open a separate form with the record details on it.

May be personal preference, but one reason to change the list box to a combo box is that a combo box is more flexible when you have more records to display. List boxes get rather unwieldy when showing more than a few lines of records.
Jul 6 '07 #6
Scott Price
1,384 Expert 1GB
Sorry I didn't click in at first that you had the list box in form header w/ the records in the main form. I'm assuming also that the main form is in continuous form view, and you have too many records to view at one time on the form.

Is there any way to filter the # of records down so that only a limited # are viewable at one time? If so it would almost do away with the need to set the focus on the record selected in the list/combo box, as the mouse movement to the top of the form to select the record would be more than the mouse movement to the middle of the form to select the record itself?

Alternatively, you could also use a filter to restrict your form to only the record selected in your list/combo box. Code to do that would look something like this:

Expand|Select|Wrap|Line Numbers
  1. Private Sub [ComboBoxName]_AfterUpdate()
  2.    Dim RecordLookUp As Integer
  3.    Me![ComboBoxName].SetFocus
  4.    RecordLookUp = Me![ComboBoxName].Value
  5.    Me.Filter = "[RecordID] = " & RecordLookUp
  6.    Me.FilterOn = True
  7. End Sub
Again assuming that your list/combo box bound column is to RecordID. Replacing the bracketed values with the valid names in your database should do it.

Anyway, hope this helps get you a bit further down the road!
Jul 7 '07 #7
Thanks, Scott, for all your input. I actually found the code that will do exactly what I want to do. So, to help out other people who may have this problem, here it is:

Private Sub ListBox_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.ListBox) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[RecordID] = """ & Me.ListBox & """"
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If

End Sub

Note that the bound colum in your List or Combo Box must be Record ID.
However, this code is subject to the 'Bookmark Bug' if you use Access 2000 and earlier.


Sorry I didn't click in at first that you had the list box in form header w/ the records in the main form. I'm assuming also that the main form is in continuous form view, and you have too many records to view at one time on the form.

Is there any way to filter the # of records down so that only a limited # are viewable at one time? If so it would almost do away with the need to set the focus on the record selected in the list/combo box, as the mouse movement to the top of the form to select the record would be more than the mouse movement to the middle of the form to select the record itself?

Alternatively, you could also use a filter to restrict your form to only the record selected in your list/combo box. Code to do that would look something like this:

Expand|Select|Wrap|Line Numbers
  1. Private Sub [ComboBoxName]_AfterUpdate()
  2.    Dim RecordLookUp As Integer
  3.    Me![ComboBoxName].SetFocus
  4.    RecordLookUp = Me![ComboBoxName].Value
  5.    Me.Filter = "[RecordID] = " & RecordLookUp
  6.    Me.FilterOn = True
  7. End Sub
Again assuming that your list/combo box bound column is to RecordID. Replacing the bracketed values with the valid names in your database should do it.

Anyway, hope this helps get you a bit further down the road!
Jul 12 '07 #8
gcoaster
117 100+
This Post REALLY helped me out! thank you!!
Oct 10 '07 #9
Finding this really useful too, been so long since i've done anything in databases. I've inputted the code above, but I cant for the life of me remember what 'record id' is, someone please help. I really need to know how to make the form show the data of the selected record in my list.

Thanks
Mar 31 '08 #10
Stuey
1
There's a simpler solution than this, or this is how I would do it anyway.

Firstly, your combo box needs to be unbound, ie. not attached to a recordset. Simply look up the values you want with a query in the properties for the combo, but have the bound column connected to the "ID" field. (Have the ID as the first field in the query, then set the combo to show two fields, but set the size of the first to 0) This isn't always necessary but it's better practice and saves confusion if you have two records with the same name - the ID field should be unique.

Then, in your form, have the content of the form based on a query which selects records from your table based on the criteria listed in your combo box.

Finally, set the "After Update" event to "Requery" and whenever you change the entry in the combo box the form will update to show the relevant records.

That sounds more complicated than it is, try it and you'll see how simple it is.



@AccessThis
Jun 6 '12 #11

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: imranzafar | last post by:
hi! i am beginner c++ programmer. Here is my code. I can delete any entered record but if i want to delete first and last node of the list it goes into indefinite loop, although the compilation is...
33
by: n00m | last post by:
import socket, thread host, port = '192.168.0.3', 1434 s1 = socket.socket(socket.AF_INET, socket.SOCK_STREAM) s2 = socket.socket(socket.AF_INET, socket.SOCK_STREAM) s2.connect((host, 1433))...
4
by: DS | last post by:
How does one open a form from a List Box? I want to to choose the record from the List Box to reopen it on another form. I've got the list box. I can choose a record in the List Box. But when I...
4
by: smshahriar | last post by:
Hi, I want to scan from the following string all the hex numbers and populate an array of integers: 0x27 0x00 0x30 0x00 0x33 0x00 0x36 0x00
6
by: AA Arens | last post by:
Hi, I have a database with 2 main forms. Contacts and companies. I share the base with two others via LAN. On the companies form I have buttons to navigate throught the records (>400). We are...
3
by: bjhecht | last post by:
http://rafb.net/paste/results/tJoB4z75.html After executing the following code I receive the errors: a.c: In function `insert': a.c:59: error: incompatible types in assignment I have no...
1
by: dabbakal | last post by:
Hello, am a new member and this is my first posting. C++ is the first progrsaming language am taking and is just for 3 months. Having benefited from lot of posting i decided to join. But currently...
25
by: beginner | last post by:
Hi, I am wondering how do I 'flatten' a list or a tuple? For example, I'd like to transform or ] to . Another question is how do I pass a tuple or list of all the aurgements of a function to...
6
by: mattmao | last post by:
Okay, this is just my exercise in order to prepare for the coming assignment regarding the damned Linked List issue... The task is simple and I am about to finish it. However, I couldn't go around...
4
by: davidcollins001 | last post by:
Hi, I am trying to get more to grips with pointers in C so I am trying to make a program that reads a file, places the data in a struct then sorts it using qsort. I have just about got my head...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.