Passing data from query to a txtbox on a Form

brucey54 · Jan 7, 2014

Hi Folks, need some help here,
I have a form with a date field, when the user creates a new record, I would like the date field to automatically fill with the most up-to-date date from the Orderdate table.

Basically I need the code to do the following when a new record is created;

Search the Orderdate table for the most recent date and then auto fill the date field on the form with that date!!!! Easy peasy.

My thinking so far...

Private Sub Command34 – where would you set this event on the properties i.e. Before update or On Got Focus ?
Docmd.OpenQuery "QryFindMaxDate"
I'm not really sure how to pass the date to the text box on the form,
End Sub
Thanks in advance

pr2-eugin · Jan 7, 2014

In the Form Current..

Code:

Private Sub Form_Current()
    If Me.NewRecord Then Me.yourDatFieldName = DMax("yourOrderDateFieldName", "yourTableName")
End Sub

Or even set the Default value of the Form control..

brucey54 · Jan 7, 2014

Hi Paul, your code worked great, thanks, is it possible to lock down the date field on the form so that users cannot edit it or change it i.e have the date field greyed out when a new record is created?

pr2-eugin · Jan 7, 2014

So I take leave it free to edit if not a new record? If so..

Code:

Private Sub Form_Current()
    If Me.NewRecord Then 
        Me.yourDatFieldName = DMax("yourOrderDateFieldName", "yourTableName")
        Me.yourDatFieldName.Enabled = False
    Else
        Me.yourDatFieldName.Enabled = True
    End If
End Sub

If you want it permanently locked change it in the Form Design.. Click the control and then Set the Enabled property to be No

brucey54 · Jan 7, 2014

Hi Paul, the code is producing a error when I open up the form, the error says
“Else without if”
not sure why as the code has a if statement and end if?

pr2-eugin · Jan 7, 2014

Could you show the code you have? The code I gave in Post#2 is a single line, the second (i.e. Post#4) is multiple line.. You might have..

Code:

Private Sub Form_Current()
    [COLOR=Red]If Me.NewRecord Then Me.yourDatFieldName = DMax("yourOrderDateFieldName", "yourTableName")[/COLOR]
        Me.yourDatFieldName.Enabled = False
    Else
        Me.yourDatFieldName.Enabled = True
    End If
End Sub

Which is wrong !

brucey54 · Jan 7, 2014

I retyped the code a new error message

“You can't disable a control while it has the focus"

Private Sub Form_Current()
If Me.NewRecord Then
Me.MealDate = DMax("MealDate", "TblDietPlan")
Me.MealDate.Enabled = False
Else
Me.MealDate.Enabled = True
End If
End Sub

pr2-eugin · Jan 7, 2014

brucey54 said:
I retyped the code a new error message

“You can't disable a control while it has the focus"

I answered in the other thread !

Code:

Private Sub Form_Current()
    If Me.NewRecord Then
        Me.MealDate = DMax("MealDate", "TblDietPlan")
        [COLOR=Blue][B]Me.someOtherControl.SetFocus[/B][/COLOR]
        Me.MealDate.Enabled = False
    Else
        Me.MealDate.Enabled = True
    End If
End Sub

Passing data from query to a txtbox on a Form

brucey54

Registered User.

pr2-eugin

Super Moderator

brucey54

Registered User.

pr2-eugin

Super Moderator

brucey54

Registered User.

pr2-eugin

Super Moderator

brucey54

Registered User.

pr2-eugin

Super Moderator

Similar threads

Users who are viewing this thread