DblClick

[Mark Roberts]

I created a copy of my "Customer" form and called
it "Browse Customers". I set the properties to Default
View-Continuous; Views Allowed-Datasheet. I added a
command button to the "Customer" form called "Browse
Customers" so that when I want to see the entire list of
customers it opens the datasheet. Now I want to be able to
select a customer record from the datasheet, DblClick it
and open that customers form for editting. When I select a
record and DblClick it, I get the same form everytime, no
matter what customer record I selected. I am using the
following:

Private Sub Form_DblClick(Cancel As Integer)
DoCmd.Close
DoCmd.OpenForm "Customers"

End Sub

What am I missing? How do I correct this?

 

[John Vinson]

I get the same form everytime, no
matter what customer record I selected. I am using the
following:

Private Sub Form_DblClick(Cancel As Integer)
DoCmd.Close
DoCmd.OpenForm "Customers"

End Sub

What am I missing? How do I correct this?

You're missing the fact that you're just telling Access to open the
form. As your code is written, it has no knowledge (and no WAY to
know) which record you happened to click.

Move the Close after the open (you still need data from the calling
form). Put in the optional WhereCondition argument to OpenForm:

Dim strWhere As String
strWhere = "[CustomerID] = " & Me!txtCustomerID
DoCmd.OpenForm "Customers", WhereCondition := strWhere
DoCmd.Close acForm, Me.Name ' close this form, not the other one

txtCustomerID should be replaced by the name of the control on the
continuous form which contains the ID field, of course.

 

[Mark Roberts]

John,

I have tried this about fifty times already. Here's what I
have-

Private Sub Form_DblClick(Cancel As Integer)

Dim strWhere As String
strWhere = "[CompanyName] = " & Me!CompanyName
DoCmd.OpenForm "Customers", WhereCondition:=strWhere
DoCmd.Close acForm, Me.Name ' close this form, not the
other one

End Sub

I keep getting the following error message:

Run-time Error '3075':
Syntax error (missing operator) in query
expression '[CompanyName]
= Sample, Inc.'.

What should I do?

-----Original Message-----

I get the same form everytime, no
matter what customer record I selected. I am using the
following:

Private Sub Form_DblClick(Cancel As Integer)
DoCmd.Close
DoCmd.OpenForm "Customers"

End Sub

What am I missing? How do I correct this?

You're missing the fact that you're just telling Access to open the
form. As your code is written, it has no knowledge (and no WAY to
know) which record you happened to click.

Move the Close after the open (you still need data from the calling
form). Put in the optional WhereCondition argument to OpenForm:

Dim strWhere As String
strWhere = "[CustomerID] = " & Me!txtCustomerID
DoCmd.OpenForm "Customers", WhereCondition := strWhere
DoCmd.Close acForm, Me.Name ' close this form, not the other one

txtCustomerID should be replaced by the name of the control on the
continuous form which contains the ID field, of course.


.

 

[Tom Wickerath]

Try the following alteration to your strWhere line:

strWhere = "[CompanyName] =" & "'" & Me![CompanyName] & "'"

Note: "'" is a single quote ' surrounded by two double quotes (ie. double quote + single
quote + double quote).

Tom
___________________________________

John,

I have tried this about fifty times already. Here's what I
have-

Private Sub Form_DblClick(Cancel As Integer)

Dim strWhere As String
strWhere = "[CompanyName] = " & Me!CompanyName
DoCmd.OpenForm "Customers", WhereCondition:=strWhere
DoCmd.Close acForm, Me.Name ' close this form, not the
other one

End Sub

I keep getting the following error message:

Run-time Error '3075':
Syntax error (missing operator) in query
expression '[CompanyName]
= Sample, Inc.'.

What should I do?

-----Original Message-----

I get the same form everytime, no
matter what customer record I selected. I am using the
following:

Private Sub Form_DblClick(Cancel As Integer)
DoCmd.Close
DoCmd.OpenForm "Customers"

End Sub

What am I missing? How do I correct this?

You're missing the fact that you're just telling Access to open the
form. As your code is written, it has no knowledge (and no WAY to
know) which record you happened to click.

Move the Close after the open (you still need data from the calling
form). Put in the optional WhereCondition argument to OpenForm:

Dim strWhere As String
strWhere = "[CustomerID] = " & Me!txtCustomerID
DoCmd.OpenForm "Customers", WhereCondition := strWhere
DoCmd.Close acForm, Me.Name ' close this form, not the other one

txtCustomerID should be replaced by the name of the control on the
continuous form which contains the ID field, of course.


.

 

[John Vinson]

I keep getting the following error message:

Run-time Error '3075':
Syntax error (missing operator) in query
expression '[CompanyName]
= Sample, Inc.'.

Sorry! I assumed (without checking or telling you) that you had a
numeric companyID (which would be a good idea, IMO; company names can
change and can be mistyped). For a Text field you need the
syntactically required quotemarks. To cover the case that the company
name might contain an apostrophe, use a doublequote delimiter (ASCII
code 34):

strWhere = "[CompanyName] = " & Chr(34) & Me!CompanyName & Chr(34)