Showing the ID in the form

  • Thread starter Thread starter Public
  • Start date Start date
P

Public

Hi,
I am making a form that is gathering data from multiple tables that have
relationships with each other. As you know, the relations are built on
primary keys (that are numbers) and then I have to show these numbers on the
form to be able to change the relationship. For my convenience, I am also
showing the description of the number (id) to make the things more readable.
This is making my form space doubled (since I have to put the ID and the
description on the form). Is there a way where I can see the description only
and change the description only, but actually it changes the underlying ID?
 
Unless I'm missing your point, that's what combo boxes are for.

Set the combo's RowSource to something like the following:
SELECT ID, somedescription FROM xyz WHERE a = b

Make sure to change the field names and the table name to suit your
scenario.

....then set the combo's other properties like so:
ColumnCount: 2
ColumnWidths: 0cm; 3cm (or inches if you prefer)
ListWidth: 3.1cm (or inches if you prefer)

Then set the combo's ControlSource to the name of the ID field.

When the user selects an item from the combo, they see only the description,
but the database records its ID. When the user selects a different
description, the underlying ID is also changed to that associated with the
newly selected description.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
Thanks for your reply.
I have tried your solution and I faced the following problems:
1) When I made the length of the first column 0, only the description was
displayed but I was not able to insert any new IDs
2) When I made the length something like 0.009, again only the description
was displayed and I was able to insert new IDs. However, when I insert the
new ID, it shows the ID and not the name (I want also the caption of the
combo box) to display the description when I insert or select a new column.

Regards
Salman
 
Which is more important from the user's perspective; the ID or the
description? Normally, IDs are internal to the system and are not displayed
to the user. Certainly, if all you're trying to do is change a relationship,
then the user need not see the ID at all. Here is some code that allows the
user to add a new description without having to concern themselves about
IDs. This code goes in the combo's NotInList event. You can get more
information about the NotInList event from here
http://www.pacificdb.com.au/MVP/Code/NIL.htm. Of course,this all assumes you
have a table in which all the Descriptions are stored.

Private Sub cboMyCombo_NotInList(NewData As String, Response As Integer)
Dim strSQL As String

DoCmd.Beep
If vbYes = MsgBox "This appears to be a new Description." & vbCrLf & _
"Do you want to add it to the list?", vbYesNo+vbQuestion, _
"Add new Description"

strSQL = "INSERT INTO myDescriptionList (Description) VALUES ('" &
NewData & "')"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Me!cboMyCombo.Undo
Response = acDataErrContinue
End If
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
Back
Top