[Possible VBA] Adding Data to Table via User Input

[one3nine0]

I have a table that lists dates in number format (ie 8/1/2015 is 42217 in the table). The table will be called ReportingMonth, and the column is called ReportingDate.

I want to create a macro that will prompt the user to enter in a date in the MM/DD/YYYY format, which will then add that to the list of dates but again, in NUMBER format.

What is the best way to do this?

As a side note, I will be creating some kind of Macro/Form that will run this Macro first, and then a series of other queries to be run sequentially. IF you have any suggestions on how to prevent Access from opening the prompts for are you sure you want to continue, etc, that would be helpful!

Thank in Advance!!

 

[one3nine0]

Ok, solved the message prompts when running the macro, but still need a macro to enter in User Inputs into my table in the correct format.

 

[alansidman]

Set your formats in the table design for each field. Build your form using the wizard. In this manner, all the fields become controls in your form and are bound to your form. They will inherit the formats you set in the table.

 

[HiTechCoach]

Also check the data type for the field. Is it a date/time or number?

Your date fields should have the date/time data type. The date/time data type is stored as a number that is a date serial number. This allows date math operations.

As Alan pointed out you can format the date into any format you need for sisplay.

 

[one3nine0]

I guess I wasn't exactly clear. I tried doing what you guys suggested and I ran into the same problem.

The table that has the dates, I need them to maintain their date serial number (ie 42217). In the form I want, I want to be able to enter in 9/1/2015, but have it store in the table as 42248.

Right now, it says that the format does not match, and I cannot enter in 9/1/2015 as a valid entry.

 

[alansidman]

How about this as a work around. On your form, insert a unbound control (textbox) that will accept the date format as 9/1/2015. In your current text box for the date, change the format to date serial and have it inherit the value form the unbound text box. Hide this bound serial date formatted text box on your form. Because the hidden text box is bound to your table, the date should appear in your table as dateserial.

 

[one3nine0]

How about this as a work around. On your form, insert a unbound control (textbox) that will accept the date format as 9/1/2015. In your current text box for the date, change the format to date serial and have it inherit the value form the unbound text box. Hide this bound serial date formatted text box on your form. Because the hidden text box is bound to your table, the date should appear in your table as dateserial.

Sounds like a possible workaround, but unsure of how to execute it.

 

[alansidman]

Sounds like a possible workaround, but unsure of how to execute it.

What parts of the explanation do you not understand or not able to execute. It is fairly straight forward.

 

[one3nine0]

What parts of the explanation do you not understand or not able to execute. It is fairly straight forward.

How to make the current text box (with the format for dateserial) inherit the value of the date entered in the new text box.

 

[alansidman]

With your form open in design view. Click on the dateserial text box and type =[Control holding the date]