Double click on a record in datasheet opens a form to enter data

[snehal0909]

I have a list of incidents in my Incidents Management Database. It comes up as a grid view when users open the database.

This is what it looks loke (paste the following into address bar of your browser. lh5.google.com.au/snehal.dabhi/R8oc22J_RfI/AAAAAAAABz0/bbIkCzg5whk/image001.png

I want particular incident to open in a form when users double click on any particular incident from the list above.
For example double clicking on the first incident (dated 1/03/2008 in the list above) should open the form below with THAT PERTICULAR incident. And double clicking on 22nd incident should open the following form with 22nd incident loaded, so users can edit that incident

please Paste the following into your browser to see the image.
lh6.google.com.au/snehal.dabhi/R8oc3GJ_RgI/AAAAAAAABz8/vGSjfr5rZ10/image003.png?imgmax=640

Any detailed help is much appreciated. – I am too new to understand macros and vba.
Thanks!

 

[pbaldy]

Look at OpenForm in VBA Help, specifically the wherecondition argument.

 

[snehal0909]

Look at OpenForm in VBA Help, specifically the wherecondition argument.

thanks, but tht didn't help.

 

[pbaldy]

Did you try? It will do what you're asking. Post your effort and we'll figure out what's wrong.

 

[RuralGuy]

...I am too new to understand macros and vba.
Thanks!

So does this mean we should not supply a solution that involves either VBA or Macros?

 

[snehal0909]

Sorry mate, i didn't mean to be lazy. I have been trying really hard to understand macros & VBA. I have even purchased a book & reading it.
But your reply says
"Look at OpenForm in VBA Help, specifically the wherecondition argument."

how do i find where that is? i looked up VBA section & tried searching but may be my search terms were not good enough, so i didn't get it.

I don't need you too do any work for me/ i just need you guys to tell me where i need to go from here to get the results i want.

I am willing to learn VBA & Macros.

Thanks for your replies!
Waiting......

 

[RuralGuy]

Use the CommandButton wizard to create a button that will open your form. Once that button has been created, if you look at the code behind the button you will see that the OpenForm command has been used. If you highlight "openForm" and press F1, it will take you to VBA help on OpenForm. We can then modify the code used to go to a specific record. Post back when you are ready to proceed with the code used in the CommandButton.

 

[snehal0909]

Just created the button. it opens the form easily, BUT IT DOESN'T OPEN THE FORM WITH THE SELECTED RECORD. it only opens the form with the first record all the time.

It is a macro. This is what it looks like:
------------------------
Open Form Add Edit Incidents, Form, , "[Incident ID]=" & [Incident ID], , Normal
------------------------

Now i have one table & two forms based on that table (tbl_Incident). One form is in datasheet view where i have records only to view & sort. And another form is to make changes to the records. The first form's name is INCIDENT MANAGEMENT SYSTEM and the second form's name is ADD/EDIT INCIDENT.

i dont know how to write sql statement that tells access to open that specific record in the form.

The help on OPENFORM gave me the following option to open the specific record i want

[fieldname] = Forms![formname]![controlname on other form]

i can fill in all details except one (CONTROLNAME).
what would control name would be? (i am wanting to double click on any record on the datagrid view of the form & open a form that allows me to edit THAT PARTICULAR RECORD)

I also tried "[Incident ID]=[Forms]![Add Edit Incidents]![Incident ID]"
But that doesn't work. Opens the form with 1st record loaded.

Thanks for the directions!

 

[RuralGuy]

I have no experience with macros and have no idea how you created one to open your form. Can you do it again with code this time?

Edit: BTW, what version of Access are you using?

 

[RuralGuy]

You should end up with something that looks like this:

Private Sub Command45_Click()
On Error GoTo Err_Command45_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmMain"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command45_Click:
    Exit Sub

Err_Command45_Click:
    MsgBox Err.Description
    Resume Exit_Command45_Click
    
End Sub

 

[snehal0909]

I am using Access 2007.
i created a button - then the wizard started - then selected OPEN FORM option & selected the form i wanted to open. - then i selected OPEN FORM & FIND SPECIFIC DATA TO DISPLAY then, it displayed fields on both forms (incident id on the right side form & incident id on the left side form) then FINISH.

 

[snehal0909]

there is no CommandButton.
how do i create a button using code?

When i start Button wizard, it creates button which runs on macro.

I am using Access 2007. Does it have anything to do with this?
Thanks

 

[RuralGuy]

...I am using Access 2007. Does it have anything to do with this?

Probably. I have A07 but it it not loaded right now and I've not used it much. It is quite a bit different than previous versions. We may need to wait for someone more familiar with A07 to drop by. As it stands now, I need to get up to speed on A07 anyway so I can load it and play around a bit. It might take some time to figure out what is going on. Do you care to post your db to give me something to work with? Remove any sensitive data but leave enough sample data to demonstrate the problem.

 

[snehal0909]

Here's the database

I have converted the database to access 2003 version (with a minor loss of functionality). And A2007 version database is also attached.

Thanks for your help!

Since i am posting the database i might as well mention all my problems.

1. posted in this post.
2. Data doesn't get updated automatically on INCIDENT MANAGEMENT SYSTEM form when i update the records in ADD EDIT INCIDENTS form. (i have to close & reopen the INCIDENT MANAGEMENT SYSTEM form to see the updated data.
3. See the attached IMAGE.

regards,
-S
View attachment Incident Management System.zip
View attachment 2003 version.zip

 

[RuralGuy]

I've got it open in A2007 for now. This might take a bit. Where in the world are you? Down Under?

 

[snehal0909]

what do you mean? (if u r talking about country: AUSTRALIA, if not then this is funny & i shouldn't have said it)

yahoo messenger id: snehal0909

Whn you open the db. it opens the form called INCIDENT MANAGEMENT SYSTEM
the database is empty, but if there were records. they should appear on that form.

And double clicking on any record should open the form (ADD EDIT INCIDENTS) - this is what i am trying to setup.

 

[RuralGuy]

BTW, here's a link to read that I'm sure is just as relevant for A2007 as prior versions.
The Evils of Lookup Fields in Tables

 

[RuralGuy]

Here's an A2007 revised db. Maybe it will give you some ideas.

 

[snehal0909]

The new form that is there is not very helpful.
I had it as datasheet so the users can sort the incidents by Incident Type - Date -Weather it's closed or not & By company name.

But this one looks a bit weird & can't sort in this form either.

(May be you're trying to show me something else that is behind the form & i am focusing on the form?)

 

[RuralGuy]

There are limitations to Datasheets that I was not able to get around. All of that sorting can be accomplished with Continuous forms as well. It just takes a little code to change the RecordSource of the form.