Search Results in a list box - populating form issues

[teachme]

Hi everyone,

I need some serious help. I just cant figure out what is going on?

First, I am not an expert but I am building and learning database at the same time.

I have a search form that searches the inventory from a table and the search results are shows at the list box below. When the user double clicks an item within the search, it opens up another form with is already populated with the details.

The issue that I am having is that after double clicking an item, the new form is not always populated with the info. from the table. Sometimes it does for certain records and sometime it does not, there is no pattern.

Any help is greatly appreciated.

Below is my code for the double click in the list box, sure sure if the problem is this.

Private Sub SearchResults_DblClick(Cancel As Integer)

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmListofItems"

stLinkCriteria = "[ContractInitiativeID]= " & Me![SearchResults]
DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub

 

[arnelgp]

how many column do you have no the listbox? does ContractInitiativeID included in your listbox. if it is point to the correct column number. listbox are zero based, means first column is index=0.

stLinkCriteria = "[ContractInitiativeID]= " & Me![SearchResults].Column(0)

 

[teachme]

how many column do you have no the listbox? does ContractInitiativeID included in your listbox. if it is point to the correct column number. listbox are zero based, means first column is index=0.

stLinkCriteria = "[ContractInitiativeID]= " & Me![SearchResults].Column(0)

Thanks for the response. I have 14 columns on the list box and yes, the ContractInitiativeID is the first one on the list. I added the column(0) as you suggested but i am still having issues.

The first 3 items (search results) pull data from the table on a form when double clicked but when i click the 4th item, it pulls a blank form even though the table still has the information that the form should populate.

Then the 5th from the list populates the form just fine, 6th is fine too but 7th record still pulls a blank form. Very frustrating...

Thank you in advance.

 

[JHB]

What is the recordsource for the form you're opening, did you check if ContractInitiativeID exist in it?
Create a new query and set the criteria for ContractInitiativeID equal to the value of Me![SearchResults].

 

[teachme]

What is the recordsource for the form you're opening, did you check if ContractInitiativeID exist in it?
Create a new query and set the criteria for ContractInitiativeID equal to the value of Me![SearchResults].

Ok thank you, its fixed now. The record source for the form that I was trying to open got screwed. When I pointed it to the correct table, it started pulling info. for all items in the list. I think this needs a bit more explanation as to what I was trying to do.

I had created an Audit Trail table to be able to track changes to certain fields (dates). A new record would get added to the Audit Trail table when a value for a field was changed. My current database is based on 1 single table but when I created the Audit Trail table, I wanted to add fields from that table to the form. So I created 1-1 relationship between the 1 table I have and the newly created Audit Table.
I then dragged and dropped a list box from the Audit Table on the form that I was trying to pull/populate. I am not sure why but when I did this the form’s record source changed from the 1 main table I had to the Audit table with 1-1 relationship with the main table.

Not sure if this makes sense but when I updated the record source with the correct table, it started to work.

Thank you all

 

[teachme]

Just wanted to post a quick update on the above issue I was having.

After my last post here, the problem still existed but it was happening less 'frequently'. After some more research, I think the problem is now fixed and it hasn't happened since I made the change below.

I had only one single form setup for both new entries (data collection) and to populate results when an item was doubled clicked from the search results and i think this was messing things up.

So I created a copy of the form and started using one same identical form to collect data (data entry with a difference name of course) and I used the other form to populate data when a search item is double clicked.

Thank you for all your comments.