If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Auto fill a field every row?
Here it is, step by step. I called the form Form1 and the date control on the
form YourDate for example. Change that line below to be the name of the date field. I am assuming that it is bound to a field in the table that is a date/time field. See my note at the bottom, though about datasheet vs. continuous form: 1. Open your form in design view. 2. Create an unbound (no control source) text box in the form's header (this way, it will not appear in datasheet view). Call it Date1 and set its format to Short Date. 3. Open the form's properties using the Properties button or right-click the form's title bar and click "Properties". 4. Click the Event tab. 5. Click inside the On Open even box until it says [Event Procedure]. 6. Click the Ellipsis (...) to the right of the On Open event. This opens the VBA window 7. Paste this underneath the Private Form_Open(Cancel As Integer): Do While IsNull(Date1) Or Date1 = "" Date1 = InputBox("Enter date") Loop The entire sub should now look like this: Private Sub Form_Open(Cancel As Integer) Do While IsNull(Date1) Or Date1 = "" Date1 = InputBox("Enter date") Loop End Sub 8. Close the VBA window. 9. Set the default value of the YourDate box to [Forms]![Form1]![Date1] The effect is this: When you open the form, a box pops up asking the user to enter a date (have the user enter it as six digits with the slashes, like: 07/31/05. It will continue to pop up until he enters something. The date entered becomes the default value for the YourDate field. Be sure to explain to the user that every new row will have this number, even if nothing else is entered; however, if the user exits the form, the newest row that has only the YourDate filled in does not get saved (Default Value alone does not make the row a new record; it must be changed or something else entered before it becomes a new record.) If the user enters something that is not a valid date, "#Error" will show up in the YourDate field. That's only because I haven't gone through all the additional steps to make sure that the input box is a valid date, since it would be much easier to simply convert your form to a Continuous Form view than to do this. If you can give up datasheet view and forcing a correct date format is important, re-post, and we can go over simplifying things using Continuous Form as the default view. Someone may have an easier way to do what you want with a datasheet view, but I stay away from datasheet view and stick with Continuous or Single form because I fell that I have more control over what goes on in the form. "Sapper" wrote: I'm not a vba man, so I'm not sure as to how or where this all happens, help? Colin. "Brian" wrote in message ... Use the default value for the date field, and make it equal to something entered once by the user. You can do it this way: Declare Date as a variable to hold the user-input date (in the General section at the top of the form's module) Private Date1 As Date Ask the user for the date when the form is opened: Private Sub Form_Open(Cancel As Integer) Date1 = InputBox("Enter date") End Sub Assign that date to the first field on all new records: Private Sub Form_Current() If Form.NewRecord Then YourDate = Date1 End Sub You could even ask the user to input the date on the form that is used to open this form, and simply make the default value of the YourDate field as [Forms]![ThatForm]![Date1]. In Single or Continuous Form view, it would be easier to assign the value to a hidden text box on the form and then refer to is as [Forms]... as above in the default value of your date text field. However, in datasheet view, even an unbound hidden text box shows up (and I don't know how to hide it). "Sapper" wrote: TIA - Novice at work. I have a datasheet form, which I cylce thru entering data, the first field is a date field which is the same date for each row, now I know I can set up a combobox for the first field, but that means I have to select the date for each cycle thru the data entry. How can I set it up so I select a date (a combobox) at the top of the form and all data entry from there on the first field is filled in using the selected date, that is untill I close the form? Then the next time I do data entry, I can select a new date etc. I know I'm missing something here, but my brian refuses to kick in. TIA, Colin |
#2
|
|||
|
|||
Thx, Brian it works as you said.
Colin. "Brian" wrote in message ... Here it is, step by step. I called the form Form1 and the date control on the form YourDate for example. Change that line below to be the name of the date field. I am assuming that it is bound to a field in the table that is a date/time field. See my note at the bottom, though about datasheet vs. continuous form: 1. Open your form in design view. 2. Create an unbound (no control source) text box in the form's header (this way, it will not appear in datasheet view). Call it Date1 and set its format to Short Date. 3. Open the form's properties using the Properties button or right-click the form's title bar and click "Properties". 4. Click the Event tab. 5. Click inside the On Open even box until it says [Event Procedure]. 6. Click the Ellipsis (...) to the right of the On Open event. This opens the VBA window 7. Paste this underneath the Private Form_Open(Cancel As Integer): Do While IsNull(Date1) Or Date1 = "" Date1 = InputBox("Enter date") Loop The entire sub should now look like this: Private Sub Form_Open(Cancel As Integer) Do While IsNull(Date1) Or Date1 = "" Date1 = InputBox("Enter date") Loop End Sub 8. Close the VBA window. 9. Set the default value of the YourDate box to [Forms]![Form1]![Date1] The effect is this: When you open the form, a box pops up asking the user to enter a date (have the user enter it as six digits with the slashes, like: 07/31/05. It will continue to pop up until he enters something. The date entered becomes the default value for the YourDate field. Be sure to explain to the user that every new row will have this number, even if nothing else is entered; however, if the user exits the form, the newest row that has only the YourDate filled in does not get saved (Default Value alone does not make the row a new record; it must be changed or something else entered before it becomes a new record.) If the user enters something that is not a valid date, "#Error" will show up in the YourDate field. That's only because I haven't gone through all the additional steps to make sure that the input box is a valid date, since it would be much easier to simply convert your form to a Continuous Form view than to do this. If you can give up datasheet view and forcing a correct date format is important, re-post, and we can go over simplifying things using Continuous Form as the default view. Someone may have an easier way to do what you want with a datasheet view, but I stay away from datasheet view and stick with Continuous or Single form because I fell that I have more control over what goes on in the form. "Sapper" wrote: I'm not a vba man, so I'm not sure as to how or where this all happens, help? Colin. "Brian" wrote in message ... Use the default value for the date field, and make it equal to something entered once by the user. You can do it this way: Declare Date as a variable to hold the user-input date (in the General section at the top of the form's module) Private Date1 As Date Ask the user for the date when the form is opened: Private Sub Form_Open(Cancel As Integer) Date1 = InputBox("Enter date") End Sub Assign that date to the first field on all new records: Private Sub Form_Current() If Form.NewRecord Then YourDate = Date1 End Sub You could even ask the user to input the date on the form that is used to open this form, and simply make the default value of the YourDate field as [Forms]![ThatForm]![Date1]. In Single or Continuous Form view, it would be easier to assign the value to a hidden text box on the form and then refer to is as [Forms]... as above in the default value of your date text field. However, in datasheet view, even an unbound hidden text box shows up (and I don't know how to hide it). "Sapper" wrote: TIA - Novice at work. I have a datasheet form, which I cylce thru entering data, the first field is a date field which is the same date for each row, now I know I can set up a combobox for the first field, but that means I have to select the date for each cycle thru the data entry. How can I set it up so I select a date (a combobox) at the top of the form and all data entry from there on the first field is filled in using the selected date, that is untill I close the form? Then the next time I do data entry, I can select a new date etc. I know I'm missing something here, but my brian refuses to kick in. TIA, Colin |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How do I auto fill a field based on info input into another field. | Brenda F | Using Forms | 1 | April 20th, 2005 03:29 AM |
Follow up Question – Convert one field into three fields | Doug | General Discussion | 2 | April 9th, 2005 10:21 PM |
Syntax needed to get needed reports | Frank Lueder | New Users | 15 | January 6th, 2005 09:39 AM |
Auto fill field in a table | Brent | General Discussion | 2 | November 12th, 2004 09:52 PM |
auto fill based on another field | bill | Setting Up & Running Reports | 3 | July 15th, 2004 06:25 PM |