Now() Time-stamp creating new unwanted blank record (1 Viewer)

[stotan7]

So for argument sake lets assume that what I am trying to do has to be done in a table and without the use of forms (becasue its what works for us) in Access 2007.

I have a table where my staff records a number of different data fields for accounts. I currently have a 3 table relationship established. One of these table is our "Notes" table. My staff enters continuous notes for different accounts as new information come in.

What I have done is set my "date" field's default value to "now()" which accomplishes the time-stamp I need, but at the same time as soon as they type anything in another field in that row Access creates another row which is unwanted. Can I prevent this, and as a follow up how can I go about also having a field that auto fills w/ the user's name/ID?

 

[GinaWhipp]

Welcome to the Forum!

This is standard behaviour for Access. As soon as you start to type in a new record it becomes the *old* record and a *new* record is created. No way to prevent that. Hmm, let me rephrase, there is a way if you were using a Form. Users could enter their new data in fields placed in the Forms Header which would be unbound then they would press a Save button which would APPEND it to the Table.

Is the User Name and ID somehow *linked* related to the Table? Side note: This would be easy if you were using a Form. And just in case you weren't aware, you can set up your Form to *look like* a table which would then give you a lot more flexibility.

 

[Galaxiom]

For arguments sake let's assume my staff must cross shark infested waters but I don't want to use a boat because that suits my business.

Staff keep getting eaten by sharks.

What should I do?

 

[vbaInet]

For arguments sake let's assume my staff must cross shark infested waters but I don't want to use a boat because that suits my business.

Staff keep getting eaten by sharks.

What should I do?

Ensure you have enough staff to enable you safely cross the waters.

 

[GinaWhipp]

Hmm, perhpas I should have welcomed you to the Asylum!

And, yes, Galaxiom's analogy is a good one because the truth is Users should enter data via the Tables and you con't control any mistakes that might happen like accidental deletion of a Record. So, perhaps you can explain why you aren't using a Form?

 

[stotan7]

To be very honest, I have no formal training in access. Everything i know I have taught myself. Our business has always just used the tables for data entry because it is familiar to staff members with little to no technical skills (i.e. excel) I have made improvements since i took it over (linked tables, updated data formats) but I have no expereince with forms. If it is a better way, and can still look familiar to my staff (require little training) while giving me more control I am all for it.

 

[GinaWhipp]

Yes, there is a better way by using a COntinuous Form AND you can use a little code to even make it navigate like Excel, for example, using the arrow keys to move up/down/sideways.

Then your request (initial post) becomes doable with greater ease. The first thing you would need to do is create a Form and set it up as Continuous. Then move your Controls around (Fields are referred to as COntrols when on FOrms) and get them in the right order. Once you get that done post back. If you need additional help with the above post back, we're here to help!

 

[stotan7]

What I currently have going is this:

A 3 Table relationship:
"AR Journal" (posting payments to an account) w/ a 1 to 1 relationship w/ "Claim Info" (contains info pertaining to the account and is basically touched once then used for reference). "Claim Info" has a 1 to many relationship with "Notes" (as follow up is done on an account new notes are added)

Currently the staff opens the AR Journal table finds the account the are following up on, clicks the "+" to open the associated account in "Claim Info" and finally clicks the "+" there to open the related part in the Notes table to create a new record (i.e. make a new note on the account).

With this current setup, do you believe I could create a form that would still accomplish this and have a familiar flow for my staff?

 

[GinaWhipp]

Yes, I do and it could be something like this...

In the Header section of the Form look up the account they want to update. In the Detail section, set up like Datasheet, click the little plus sign to open notes OR you can also give them a Command Button. BUT if you don't want the new record to appear would use a sperate Form for notes, activated by a Command Button to open up an UNBOUND Form. EIther way will also be able to capture Users ID either via their Network ID or if they are logging on.