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

[RuralGuy]

I just thought of another way that might be able to use the Datasheet. I'll work on it.

 

[snehal0909]

I will attach a database that does exactly wht i want. I think it's from MS Office 2007 Templates (Microsoft Templates online). Let me search & find it. May be you'll understand exactly what they have done in that database & if the same can be done in my db.

-----------------
Sorry just realized that i can't open Access 2007 db as i only have 2003 version (i m at work).
But one of the databases from this page.
http://office.microsoft.com/en-us/templates/CT101428241033.aspx
I think it's either ISSUES or TASKS or INVENTORY, tht has the datasheet view as default & then you can double click to edit the record.

See if you can find it, if not, i will confirm which one it is & will attach it to this thread this eve (it's 12:30pm here).

Thanks!

 

[RuralGuy]

See if this db is more what you had in mind.

 

[RuralGuy]

So how did you figure out the db I 1st uploaded would not do if you only have A2003?

 

[snehal0909]

OWESOME!!!

That's exactly what i wanted.
What did you do?
Why are the field name wierd? i changed the name of "txtEdit" field to edit (both in NAme & in code) but then i got error.

But this is certainly good progress. I will look into the code & the hidden button. & see if i can change the names.

Thanks heaps.
Will post the results soon (this eve)

 

[snehal0909]

Company's file server has Office 07. I have to remote desktop to server & each time i want to open any Access07 files. (i have to make it quick coz other people use remote desktop for banking & stuff, but at home i have time to download - play with templates).

 

[snehal0909]

Finally I found the file.
It's attached.

View attachment Tasks.zip

 

[RuralGuy]

Here's the db with the Datasheet Names fixed. There is a property under the "Other" tab called "Datasheet Caption".

 

[snehal0909]

Thank you!

Thank you Rural Guy!

Perfect!!!

 

[RuralGuy]

You're welcome. I'm working on the other problems now. One fixed already. Will post a new db when all are working as needed.

 

[RuralGuy]

I fixed the updating problem when adding new records. FYI this db does not have anything on the Witness tab so I'm not sure what the picture you posted means.

 

[snehal0909]

you know how Incidents Table & Witness table both have INCIDENT ID field. I am trying to create a child form in the witnesses tab, which shows ONLY THE WITNESSES RELATED TO THAT PARTICULAR INCIDENT.

See attached database: (especially WITNESSES TAB)
Now the witnesses tab has datasheet view of all the witnesses RELATED TO THAT INCIDENT. and when you change incident, the witnesses will change as well. (so that datasheet view of the child form is connected to incidents somehow.

But another form tht should pop up when user clicks on the witness datasheet (in witness tab) is not connected to anything - i haven't figure it out yet. So if the user adds witnesses to it, then those witnesses will not be connected to any incidents (redundant witnesses). But there must be some way to connect it to

Man i need to learn VBA.

I am wanting to do the same for all other tabs (actions - Insurence tab will have communications with insurer in datasheet view - Property damage tab will have Property & damage details from tbl_property & injury recovery will have injury recovery process in datasheet view)

View attachment IncidentManagement2007-2.1.zip

 

[RuralGuy]

Since you are just starting out, you should *really* adopt a Naming Convention and then stay consistant. Now is the time to start developing good habits!

 

[snehal0909]

thanks. I just read about naming conventions in my book & i was redesigning the same database with proper names for tables. It's like all these things (you -the book & the database) are trying to teach me naming conventions. Also will try to get rid of those lookup tbles. Will Post the results.

Thanks!

 

[RuralGuy]

Don't get rid of the LookupTables unless that are not needed. Just eliminate the "LookupFields" in your tables and do the lookup with ComboBoxes in your forms. Here's some other links of words and characters *not* to use!
List of reserved words in Access 2002 and Access 2003
List of Microsoft Jet 4.0 reserved words
Special characters that you must avoid when you work with Access databases
Problem names and reserved words in Access

 

[snehal0909]

what do you suggest?

If i keep the lookup ables,will i be able to pull a report that is based on lookup table?
e.g: a report that shows all incidents that involve death. (i think tht will use incident type look up table)

also there is another way - having lookup fields without actually creating lookup table. run lookup wizard then it will give you two options - the second option is "i will type in the values tht i want".
if i create lookup tables that way, will i still be able to pull reports based on that data?
e.g: a report that shows all incidents that involve death. (but there is no look up table here for incident type)

 

[RuralGuy]

Do not create LookupFields either way! You can use joins in a query to replace your keys with TEXT from the LookupTables.

 

[snehal0909]

I am creating the database again with proper names (after reading naming conventions).
I am having hard time understanding how you have created lookup drop down boxes on the form without having combo boxes.

1- Is this an advanced technique? can i do it myself in my new database? How?
2- DO I NEED LOOKUP TABLES?
3- How do users add their own options in drop down list on the form? (example: how do users add more companies in select company drop down list?)


Greatly confused.

 

[RuralGuy]

You create the ComboBox at the form level using the ComboBox wizard. These cbo's can certainly reference tables for their Lookup. If you choose to call these tables Lookup Tables, that's fine.

When you defined a lookup for a field at the table level, the form creation used this information to assign a control for that field. The ComboBox that was created on the form was actually a stand alone control that was complete. It no longer needed the field to have a lookup. The field lookup could have then been changed to a TextBox without affecting the form in the least. When you look at a table directly in datasheet mode and there is a Lookup Field, what you see is the lookup value and not what is really in the field. When the Lookup for the field is changed to a TextBox, the same datasheet view of the table reveals what is actually stored in the field.

 

[snehal0909]

Aha, So i create lookup table & use the wizard to create drop down control on form BUT THEN CHANGE IT TO TEXT BOX & that does the trick.

But if i wanted users to have an option to edit that drop down list, can i create a form based on lookup table & let users edit the values that come up in drop downlist using that form?

Is that the right way? is there a better way?