Clicking a record to pull up a form (1 Viewer)

[mgmercurio]

I am betting this is n easy one for the experts...but again...not so easy for me....

Is there a way to click a specific record in a datasheet (a query placed on a form in datasheet view) to pull up the form with that record in edit mode?

I have a query that tracks "sales touches" against each account listed in the tbl_accounts table. I have a form that allows my user to enter specific info concerning the touch i.e. touchdate, description, notes, ContinueTouch(checkbox), and NextTouchDate. The query displays the next touch date in a form (my dashboard) by the criteria I set i.e. NextTouchDate - criteria =Date() and ContinueTouch checkbox is True.

The results of this query is displayed on my dashboard form in the view of a datasheet. I want to be able to click the AccountName in the datasheet to pull up the SalesTouch Form so I can enter another touch regarding that account.

I am fairly confidant this is probably easy for the experts and I am certain it involves a doubleclick event in the column AccountName...but I can't figure it out.

Any ideas is appreciated.

Thanks,
mgm

 

[MajP]

DoCmd.OpenForm method (Access)

Office VBA reference topic

docs.microsoft.com

look at the example with a where condition
yours may look something like
docmd.openform "SalesTouchForm",,"AccountID = " & me.ID

Double check my number of commas

 

[mgmercurio]

DoCmd.OpenForm method (Access)

Office VBA reference topic

docs.microsoft.com

look at the example with a where condition
yours may look something like
docmd.openform "SalesTouchForm",,"AccountID = " & me.ID

Double check my number of commas

I took your advice and I certainly see the logic and that this should work...however, I am getting the following error message as in the attached screenshot...any advice? Note - I commented out my first attempt...as you can see.

 

[MajP]

I guessed at you name. What is the primary key? If it is a string you have to use single quotes.

"AccountName = '" & me.AccountName & "'"
this turns it into something like
AccountName = 'Smith'

Or
"AccountID = " & me.ID
returns something like
AccountID = 123

What are the real names or your fields.

 

[mgmercurio]

I guessed at you name. What is the primary key? If it is a string you have to use single quotes.

"AccountName = '" & me.AccountName & "'"
this turns it into something like
AccountName = 'Smith'

Or
"AccountID = " & me.ID
returns something like
AccountID = 123

What are the real names or your fields.

Ahhhhh gotcha...well I am using the AccountName in my query and on the frm_AccountSalesTouches form. I do have the AccountID on my form as well. Regardless I think you are on to it...with the single quotes idea...

Also...here is a shot of the tbl_Accounts table....

 

[mgmercurio]

It worked! I took your advice and changed it to single quotes and it is now working.

Thank you for everything!

 

[MajP]

FYI,
That example uses a rarely used format for calling a function, which might seem strange.

The Docmd.open form has a lot of arguments but only the first one is required. The name of the form. The fourth argument is the where condition. So for the optional arguments, you type the comma with no information. The 4th argument is the where condition

DoCmd.OpenForm "Employees", , ,"LastName = 'King'

Rarely used is calling the argument by name instead of just putting commas.
DoCmd.OpenForm "frmOrder", WhereCondition:="CustomerID=" & Me.txtCustomerID

then you use the name with like
WhereCondition:=
or
openArgs:=

 

[Pat Hartman]

Your AccountID is not a string so enclosing it in quotes should not have fixed the problem. Please post your solution.

 

[MajP]

They used the accountname see thread.

 

[Pat Hartman]

@MajP
I was looking at your code.

@mgmercurio
Unless the AccountName is UNIQE AND indexed, you shouldn't be using it. It is better practice to use the AccountID which IS unique and IS indexed. The AccountID does not have to be visible but it does need to be included in the Form's RecordSource. It doesn't matter what control's double-click event you use. Any of them can reference the AccountID.

 

[mgmercurio]

Sorry folks..Ive been out of pocket for a few days and Im just now seeing the responses....

Anyway...yes I agree..I should be using AccountID instead...and I will try to make it work... I realize I have to use the argument for number instead of a string...which I will change and test.

And to MajP's point...I did use AccountName so that did fix it...but like you guys are rightfully pointing out...its a band-aid fix that will surely come off at some point

thanks to both of you for your guidance!
-mgm

 

[MajP]

And to MajP's point...I did use AccountName so that did fix it...but like you guys are rightfully pointing out...its a band-aid fix that will surely come off at some point

Even if your account name is unique, which it likely is. Searching on strings can be problematic. Sql can have trouble with some characters and you have to handle them special.

If your account name had a single quote or a # it will fail
Jim's Auto Shop
Jim's #1 Barber Shop

I have been working with a guy in Spain with Spanish accented characters. These all have to be handled. Worse is some records will include the accents and some will not.
So use a numeric ID whenever possible. Also a little faster for the db engine to search on a large database.

 

[mgmercurio]

Well good news...I converted it all over to use AccountID and we are all good. Also just as a side note...the AccountNames were all unique..I spent hours doing that on the spreadsheet before I brought it in to the DB

 

[arnelgp]

well, ChrisO, made a good code, instead of passing a Criteria to the OpenForm
method, he passes the recordset as OpenArg parameter.
so there is no need to determine the PK key.
in fact, on the sample db, there is No PK key for the single table.

 

[Pat Hartman]

the AccountNames were all unique

To keep them that way, make sure you have a unique index on that column in addition to the autonumber PK.