double click on listbox problem

[krowe]

This is something i have done many times before, but today for some reason it wont work.

I have a search form with a list box which shows the results.

i have this code on the double click event:

  Dim stDocName As String
   Dim stLinkCriteria As String
   stDocName = "frmAllContactsSummary"
 
   stLinkCriteria = "[ContactID]=" & Me![SearchResults]
   DoCmd.OpenForm stDocName, , , stLinkCriteria

frmAllContactsSummary opens with a filter applied, however it opens at a new record, not the one where ContactID=SearchResults.

I have checked the bound column in the list box, which is 1 (which is the correct field for ContactID) and when I go to design view on frmAllContactsSummary the filter displays the correct ContactID number.

Can anyone suggest any other settings i may need to check to see why this may be happening.

Thanks

Kev

 

[AccessMSSQL]

Check the forms data entry property . It may be set to Yes? Check the record in the table corresponding to ID=1. Perhaps the data in the table fields accidentally got deleted - leaving a blank record?

 

[krowe]

Hi

thanks for your quick response.

The data entry property is set to No on the form.

I have checked the tbl behind and there are values in every ContactID record. they start at about 509000 (due to it being a table created every time the DB is run appending 3 other tables).

any other ideas?

 

[AccessMSSQL]

hmmm..hard to say what's going on. Have you checked the filter property of the form to make sure there aren't any filters stuck there that may be affecting it? Also, check the recordsource of the form - maybe the query has some criteria in it that is preventing record 1 to show up? What happens if you just open the form from the navigation pane? Are there any On Open events of the form that's doing any processing?

 

[rodmc]

Dim stDocName As String
   Dim stLinkCriteria As String
   stDocName = "frmAllContactsSummary"
 
   stLinkCriteria = "[ContactID]=" & Me![SearchResults]
   DoCmd.OpenForm stDocName, , , stLinkCriteria, [B]acEdit[/B]

I know thats just what mode the forms opens in, but give a shot anyway

 

[krowe]

Hi

I was not around yesterday but just seen your response.

Thank you for your help, looking at the tables has helped identify the problem, but unfortuantely I dont know the answer...

The table that the search works from is created as the form opens, compiling data from 4 other tables (see the onopen event). At the point it gets new unique ID's.

The query to run the search seems to be using old data so the ID's do not match.

I have copied the tables/queries and forms into a new DB and stripped out the data.

Please could you take a look and see if you can work out how i can reolve this problem. The form you need to start with is frmSearchAllContacts.

Thanks again

Kev

 

[AccessMSSQL]

I'm looking at the db now and I think you need to modify your table structures and the method you are using to search. Sorry to have to tell you this, but you have redundant tables. You should have all of your contacts in one table (Lenders, Agents, Landlords). Then you wouldn't have to populate another table with records from all of the other tables. This is incorrect relational design. In your main table just have a field called ContactType. This would be populated with Lender, or Agent, or Landlord. Then in your search screen, just set your listbox rowsource to a query based on the main table. No need to delete records and re-insert into a seperate table. Also, what is your tblContactDirectory table for. You have services listed in here. It looksl ike your Agent table has a few additional fields that wouldn't apply to lenders or Landlords, but that's okay. These fields can just be ignored for the other contact types. When you open your main form you can check the contact type and if it's not agent just hide those fields.

 

[krowe]

Hi thanks

I agree, however they were originally seperate databases that have been brought together into one, (a couple still exist as seperate databases and are still used, I just link to the tables as they are maintained by someone else) hense the proceedure for compiling them...

I still think that i should be able to get this working, maybe a re-query at some point would help, im just not sure where

 

[AccessMSSQL]

Ahh...I see. Okay. Why don't you pass the ID and the contacttype to the openform argument instead of the autonumber contactID? Not sure if you are also storing your contact type in the tblAllContacts but if you did you could just open the form based on ID and contact type?

 

[krowe]

Hi

I've never used openargs before, but think i need to finally get to grips with it.

I have this code now on my search page dblclick:

 Dim stDocName As String
    'Dim stLinkCriteria As String
    Dim strOpenArgs As String
    stDocName = "frmAllContactsSummary"
    strOpenArgs = "[ID]" & "|" & [Type]
    'stLinkCriteria = "[ContactID]=" & Me![SearchResults]
    DoCmd.OpenForm stDocName, , , , , , strOpenArgs

and this on the onload event of frmAllContactsSummary:

varSplitString = Split(Me.OpenArgs, "|")
  Me.[ID].Value = varSplitString(0)
  Me.[Type].Value = varSplitString(1)

I get an error which says runtime 2465, can't find the the field '|1' referred to in your expression.

 

[AccessMSSQL]

Hi krowe:
Did you resolve your issue? I've been off the forums for a week or so so sorry for the delay in response?

 

[AccessMSSQL]

Looks like ID is blank so it's not putting any value before the pipe?