Opening a Form with Specific Criteria

[wjoc1]

Hi,

I have a form which opens with specific criteria. The code I have for doing this is as follows:

 stDocName = "Network Refurbishment 20kV-QuickUpdate"
    
 stLinkCriteria = "[Segment]=" & " ' " & Me![Segment] & " ' "
 DoCmd.OpenForm stDocName,  ,  , stLinkCriteria

This works fine but I want the stLinkCriteria to include the value of another filed called [Pole No]. You see both Segment and Pole No make up the primary key for this table and I need to specify both in stLinkCriteria before opening the form to ensure the right record is opened.

Try as I might I cannot get this to work. Can someone please tell me the correct syntax?

Thanks,
Liam

 

[Rob.Mills]

Am I understanding you correctly? You have two different fields that make the primary key for a table? If that's true you probably should fix that so there's only one. That would eliminate this problem.

 

[wjoc1]

Can't I'm afraid, each segment can have a number of different poles so to ensure that I get unique records in the table I have to use the two fields combined as the primary key.

 

[Rob.Mills]

Well if each segment can have multiple poles break up all the fields that pertain to the poles into one table and establish a one-to-many relationship between the two tables. Otherwise it's going to be really hard to work with data setup that way.

 

[Rich]

Use the And operator such as
stLinkCriteria = "[Customer]=" & "'" & Me![Customer] & "'" & "And [Issued]= #" & Format(Me![DatesIssued], "mm dd yyyy") & "#"

 

[wjoc1]

Excellent, and the problem is solved! Just whipped out the # to specify my field as a number and bingo. Thanks very much!

Liam