Retrieving an incorrect record from a drop down list

[GMSRE]

Hello

I am working in Access 2007.

I have a dropdown list that shows the recorded number(RecordIDNo), community ID number (CommunityID) and community name (CommunityName )for the list of records associated with the community ID number. I want retrieve the record I click on in the dropdown list associated with the community ID. For example I have 3 records for community ID 00001, 4 and records from community ID 00005. I also have a table called “InputTable” that stores all the records in order of the record ID, then community ID number, then by state . The record I click on in the dropdown list does not always retrieve the correct record associated with the community ID. When I clicked record ID 1 I associated with community ID 00005, it retrieved record ID 1 of associated with community ID 00001 instead of community ID 00005. I noticed that it always takes the first record 1 for example. If I clicked on record ID 4 associated with community ID 00005 the correct record would be retrieved because there would not be a record ID 4 associated with community ID 00001.

Any help would be greatly appreciated. Thank you.

GMSRE

Listed below is the code I used.

Private Sub FindRECID_AfterUpdate()
''Me.FindRECID.Requery
'Find the record that matches the control.
Me.RecordsetClone.FindFirst "[RecordIDNo] = " & Me![FindRECID]
Me.Bookmark = Me.RecordsetClone.Bookmark

End Sub

Private Sub FindRECID_Enter()
On Error Resume Next
Me.FindRECID.Requery
End Sub

 

[boblarson]

Two things - I believe you have to assign the recordsetclone to a recordset to search and also you should NOT be using On Error Resume Next because it can obscure problems.

[FONT=Calibri][SIZE=3]Private Sub FindRECID_AfterUpdate()
Dim rst As DAO.Recordset[/SIZE][/FONT]
[FONT=Calibri][SIZE=3][/SIZE][/FONT] 
[FONT=Calibri][SIZE=3]'Find the record that matches the control.[/SIZE]
[SIZE=3]Set rst = Me.RecordsetClone[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]    rst.FindFirst "[RecordIDNo] = " & Me![FindRECID][/SIZE][/FONT]
[FONT=Calibri][SIZE=3]    If rst.NoMatch Then [/SIZE][/FONT]
[FONT=Calibri][SIZE=3]       Msgbox "No match was found"[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]   Else[/SIZE]
[SIZE=3]       Me.Bookmark = rst.Bookmark[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]   End If[/SIZE][/FONT]
[FONT=Calibri][SIZE=3][/SIZE][/FONT] 
[FONT=Calibri][SIZE=3]rst.Close[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Set rst = Nothing[/SIZE]


[SIZE=3]End Sub[/SIZE][/FONT]

 

[boblarson]

If RecordIDNo is text then instead of this:

[FONT=Calibri][SIZE=3]    rst.FindFirst "[RecordIDNo] = " & Me![FindRECID][/SIZE][/FONT]

you would need this:

[FONT=Calibri][SIZE=3]    rst.FindFirst "[RecordIDNo] = " & Chr(34) & Me![FindRECID] & Chr(34)[/SIZE][/FONT]

 

[GMSRE]

Hi Bob,

I greatly appreciate you getting back to me quickly. I tried the code. It still does the same thing of not always retrieving the correct record.

Thanks again.

GMSRE

 

[boblarson]

Hi Bob,

I greatly appreciate you getting back to me quickly. I tried the code. It still does the same thing of not always retrieving the correct record.

Then you probably have something not correct and it would be beneficial to actually have a copy of the DB uploaded so we can track it down quickly.

 

[GMSRE]

Hi Bob,
To Retrieve A Record:
Select a state first. Then select a County. Then Select a community. Then select a record. Here is the attached database.

Add a Record:
Select a state first. Then select a County. Then Select a community. Click Initiate Record.

Thanks again.

GMSRE

 

[boblarson]

Hi Bob,
To Retrieve A Record:
Select a state first. Then select a County. Then Select a community. Then select a record. Here is the attached database.

Add a Record:
Select a state first. Then select a County. Then Select a community. Click Initiate Record.

Thanks again.

GMSRE

From your description and looking at the database I don't understand what is supposed to happen versus what is happening.

 

[GMSRE]

Here are the two things I am trying to do:

1. Filter the add records based on the community that is assocaited by state, county and community. This part of the database is working correctly. I can add records. in the database.

2. The second part is that I am trying to be able to retrieve existing records associated with the community I am looking for. I want to have the option of being able to update or change what is in a record. The I will add data in the other fields of the record at a later time. This is one that will note always get the correct record.

I hope this answers your question.

Thanks again for your patience and help.

GMSRE.

 

[GMSRE]

Hi Bob,

Yesterday I sent you the database. Were you able to find out why I am not able to always retrieve the correct record..

Thanks again.
GMSRE

 

[boblarson]

Sorry, I had to do it from home and I had forgotten yesterday. I just looked and found why you are having the problem. You have this for a table and you don't have a unique identifier for each record so it isn't any wonder why you get incorrect results. You need to have a primary key which you can search on - OR you need to use more than just the RecordIDNo in your code as criteria. But even with that you can have non-unique rows.

 

[GMSRE]

Hi Bob,

I added an another field in the InputTable called RecComID to identify the record no associated with the communityIDNO. I changed the RecorIDNo field type to autonumber so would increment autonumber in the InputTable. I also made it a primary Key indexed with no duplicates. That solved my problem. Your help is greatly appreciated. Thanks so much.