How do i get a user input to repeat into each new record?

[Lswworth]

I want user to enter a date on a form and have that date repeat for each record on table column and new records on form.

Is this possible?

[twinnyfo]

Lswworth,

Welcome to Bytes!

That should be fairly simple. In the Form's OnCurrent Event:

Private Sub Form_Current()
    If Me.NewRecord Then _
        Me.txtFieldName = Me.txtUserEnteredDate
End Sub

Hope this hepps!

[Lswworth]

When I click on the text field on current is not listed on the property sheet.

[twinnyfo]

This is a property of the Form--not the control.

[Lswworth]

Got it.

I'm getting an error for me.txtuserenteredate

method or data member not found.

[twinnyfo]

Obviously, you must rework the code so that the names of the controls match your form.

[Lswworth]

No errors but new record is blank.
I need a user entered date box that will keep the date entered for new records without additional input.

[twinnyfo]

Ok. Perhaps I wrongly presumed that you had more experience with Access.

On your form create two text boxes. The first text box is UNBOUND to the underlying table. This is the text box that users will enter the date. You should make sure that this control only accepts a true date when data is entered otherwise you will have additional problems.

The second text box is BOUND to the underlying table and the field you want updated. Then, whenever a user enters a date, moving to a new record will update that field with the date entered by the user. There will need to be additional tweaks and perhaps adding an AfterUpdate event to the text box so that entering the date will update the current record (if you wish).

This is the basic idea.

Hope that makes more sense.

[Lswworth]

Yeah, I've been teaching myself. I've got a few spreadsheets to work like I want. Sometimes I impress myself, but this one is giving me hell. At work right now. I'll try again tonight or tomorrow.

[twinnyfo]

Let us know what kind of guidance you need and we'll see what we can do....

[Lswworth]

Okay, I finally got it working, but only for new records.
This is good, but I would also like it if when someone changes the unbound text date then dates in all records be updated to new date.

For instance, if a senior employee shares form with new employee and new employee changes hire date... this date would reflect in all their records.

Thank you again for your help.

[twinnyfo]

Your explanation now clarifies that your structure is incorrect. An Employee can only have one hire date. This hire date should not be on multiple records, but only one the ONE record pertaining to Employee Data. This date should not be on all records associated with this employee. Rather, the table with Employee data should be related to other tables that refer to employees.

[Lswworth]

Initially, I did have two tables but couldn't figure out how to link both tables.

[twinnyfo]

You should have an Employees Table with a primary Key. That table should have a field in it called HireDate, as well as all the other employee-specific data that would occur only once (FirstName, LastName, EmployeeIDNumber, etc.). Any other tables related to the Employees should only need to use the Primary Key of the Employees table to refer back to that record. This is the basic construction of all Relational database.

Sounds like you have more significant issues than the one posted.

[NeoPa]

Hi.

It looks like Twinny has given you the answer your problem really needs so I've set that as the Best Answer.

Should you need more assistance, and that's perfectly fine if you do, please post new questions in separate threads.

Bear in mind you should be looking to post your questions to ask the for the fundamental information rather than getting half way with an idea then asking for help with that. Often the reason you can't make such ideas work is that they are ideas that can't work.

Go for the fundamental question and we can help you with the right approach and, generally that way, everything's just easier.

We do understand that it's sometimes difficult to determine what is fundamental when your working in a new area so just aim for that and we'll give you the best advice we can.

[Lswworth]

Yes, that is the best answer.
Sorry, I have several projects I'm working on around the house. I had to take a break from this "work" project for a little bit. It's really only for brownie points, so no rush.

I did relate the tables by ID and made a query including both tables. Then I created a form based on that query. It looks like it might work. I have to go to work, but maybe if I have time I'll play with it tomorrow.

Thank you for all your help.