-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Adding system time at request
- Thread starter Doug123
- Start date
I tried something different. I created a macro in Excel as:
Private Sub Command29_Click()
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 1/09/2005 by hintond
'
'
ActiveCell.FormulaR1C1 = "=NOW()"
Selection.NumberFormat = "h:mm"
End Sub
In Excel this macro enters current system time when cell is activated by tab or enter. Can I do a similar thing in Access by using a similar macro in the toolbars button??
Private Sub Command29_Click()
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 1/09/2005 by hintond
'
'
ActiveCell.FormulaR1C1 = "=NOW()"
Selection.NumberFormat = "h:mm"
End Sub
In Excel this macro enters current system time when cell is activated by tab or enter. Can I do a similar thing in Access by using a similar macro in the toolbars button??
Upvote
0
Excel Facts
Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Andrew Fergus
MrExcel MVP
- Joined
- Sep 9, 2004
- Messages
- 5,462
- Office Version
- 365
- 2021
- 2016
- Platform
- Windows
If you want to insert the time when the user creates a new record in the table, then use the default setting in your table (or form) as previously suggested.
If however, you want to capture this to the second (i.e. when the bollards are lowered and the user clicks the button at the same time) then you could use a small VBA script to force the current system time into the appropriate field. You don't need a macro to do this.
If the first method (using a default value) is not correct, go into the form design, make sure you have a button already in place and the field into which you wish to capture the time. Right click the button, Click Build Event -> Code Builder -> Ok -> between the 'Private Sub' and 'End Sub' lines, enter the following -> Me.YourTimeFieldName = Now() -> Save and close the VBA editor window. {NB make sure you use your actual field name rfom the form where I have used YourTimeFieldName). Back in your form design, right click the field in question (which I referred to as YourTimeFieldName, you will find the actual name at the very top of the 'Other' properties tab) -> Properties -> Data Tab -> Control Source -> Make sure this is pointing at the correct field in your table -> Format Tab -> select an appropriate time format -> Save and give it a test run.
HTH, Andrew
If however, you want to capture this to the second (i.e. when the bollards are lowered and the user clicks the button at the same time) then you could use a small VBA script to force the current system time into the appropriate field. You don't need a macro to do this.
If the first method (using a default value) is not correct, go into the form design, make sure you have a button already in place and the field into which you wish to capture the time. Right click the button, Click Build Event -> Code Builder -> Ok -> between the 'Private Sub' and 'End Sub' lines, enter the following -> Me.YourTimeFieldName = Now() -> Save and close the VBA editor window. {NB make sure you use your actual field name rfom the form where I have used YourTimeFieldName). Back in your form design, right click the field in question (which I referred to as YourTimeFieldName, you will find the actual name at the very top of the 'Other' properties tab) -> Properties -> Data Tab -> Control Source -> Make sure this is pointing at the correct field in your table -> Format Tab -> select an appropriate time format -> Save and give it a test run.
HTH, Andrew
Upvote
0
ease20022002
Board Regular
- Joined
- Jun 4, 2005
- Messages
- 233
The easiest way to do this if your form isn't already linked is to start again and use the form wizard.
If you use the form wizard, it will ask you for the source data, which can be a table or query. Go through the wizard steps and if the end result isn't exactly what you want, go into design view and change the fields and locations.
If you don't want to use the wizard, and want to design the form anew, then you would select new form and make sure you choose the correct source data, i.e., table or query. Once you finish that process you will be in the design mode of the form. If you pass the cursor over the icons in the menu bar, you will find one that says Field List. Select that and all the fields that you want appear in the Field List Box. Drag the fields you want to the form. They automatically default to Text Boxes, you can change this by right-clicking and there should be an option to convert to another Form object, i.e., combo box. If you are using 97, I don't know. What you can do is look at the menu bar listings and see if there is a convert method.
If you still can't convert to a combo box for the GATE field, delete the TextBox for the GATE field and select the combo box from the toolbox toolbar as discussed previously. Go through the wizard and it may ask what the control source is, if it does, then it is easy, you just select the GATE field from the table you setup and your done.
I'm rambling, but you get the picture.
Lastly, if you wanted to link the form that you have as it is, you would have to select the appropriate CONTROL SOURCE for each form object. You would right-click on every object and goto control source, press the ... button that appears when you place your cursor in the CONTROL SOURCE property and then another dialogue box will appear Expression Builder. All you want to do is select the Table from the lists below the blank screen for expressions. Once you doubleclick the appropriate field, the blank sceen will have something like =GATE
let me know if you need anything else...
If you use the form wizard, it will ask you for the source data, which can be a table or query. Go through the wizard steps and if the end result isn't exactly what you want, go into design view and change the fields and locations.
If you don't want to use the wizard, and want to design the form anew, then you would select new form and make sure you choose the correct source data, i.e., table or query. Once you finish that process you will be in the design mode of the form. If you pass the cursor over the icons in the menu bar, you will find one that says Field List. Select that and all the fields that you want appear in the Field List Box. Drag the fields you want to the form. They automatically default to Text Boxes, you can change this by right-clicking and there should be an option to convert to another Form object, i.e., combo box. If you are using 97, I don't know. What you can do is look at the menu bar listings and see if there is a convert method.
If you still can't convert to a combo box for the GATE field, delete the TextBox for the GATE field and select the combo box from the toolbox toolbar as discussed previously. Go through the wizard and it may ask what the control source is, if it does, then it is easy, you just select the GATE field from the table you setup and your done.
I'm rambling, but you get the picture.
Lastly, if you wanted to link the form that you have as it is, you would have to select the appropriate CONTROL SOURCE for each form object. You would right-click on every object and goto control source, press the ... button that appears when you place your cursor in the CONTROL SOURCE property and then another dialogue box will appear Expression Builder. All you want to do is select the Table from the lists below the blank screen for expressions. Once you doubleclick the appropriate field, the blank sceen will have something like =GATE
let me know if you need anything else...
Upvote
0
I use Access 2002. I will fiddle with your suggestions. I have the combo boxes working for the Gates and Services but the field "Bollards", "Gate Alarm", "Patrol", Gate Unlocked and Gate Relocked I want to insert time when the button is pressed. I will fiddle with the suggestions and get back to you.
Upvote
0
