Solved Text Box Default Value

[Emma35]

Hi All,
I've got a form and subform as below. The main form contains details of a piece of machinery, and the subform has details of each occasion it was serviced. When i'm entering a new record on the subform, is it possible to make the date that particular machine was last serviced appear in a text box on the subform as the default value ? So really, i'd like to see the last value in the ServiceDate field for that machine displayed in the text box

Thanks

tbl_EquipmentList
EquipID PK (Short Text)
EquipmentName (Short Text)
Manufacturer (Short Text)

tbl_ServiceRecords
ServiceID PK (Autonumber)
EquipID FK (Short Text)
ServiceDate (Date)
ServicedBy (Short Text)
PassFail (Yes/No)

 

[bob fitz]

Try something like:

=DMax("ServiceDate","tbl_ServiceRecords","EquipID FK = '" & Me.[EquipID FK] & "'")

as the Default Value property setting of the text box

 

[Emma35]

Thanks bob......i gave it a try and a getting the #name? error in the txtbox

 

[Gasman]

Thanks bob......i gave it a try and a getting the #name? error in the txtbox

If you copied the post, remove the FK entries. You need field = control ?

 

[bob fitz]

Thanks bob......i gave it a try and a getting the #name? error in the txtbox

Perhaps:
=DMax("ServiceDate","tbl_ServiceRecords","[EquipID FK] = '" & Me.[EquipID FK] & "'")

 

[arnelgp]

you may also use the subform's Current event:

Private Sub Form_Current()
Me.ServiceDate.DefaultValue = Nz(DMax("ServiceDate","tbl_ServiceRecords","EquipID FK = '" & Me.[EquipID FK] & "'"), Date())
End Sub

 

[Emma35]

bob....still the same error
Gasman...thanks i did that
arnelgp......i put your code into the Form Current event and the text box now displays the date 30/12/1899 ?

 

[bob fitz]

Emma35

I think arnelgp is on the right track but I think the code needs a pair of square brackets because you have a space in the field name.

Private Sub Form_Current()
Me.ServiceDate.DefaultValue = Nz(DMax("ServiceDate","tbl_ServiceRecords","[EquipID FK] = '" & Me.[EquipID FK] & "'"), Date())
End Sub

 

[Emma35]

bob....i probably should have said earlier but my field name is EquipID. The Fk was just when i laid out the table structure i was saying this was the foreign key. I've just been altering the code to delete it. I tried the square brackets anyway and am still getting that weird date

 

[arnelgp]

what is the textbox name for EquipID?, you put it on the expression

Private Sub Form_Current()
Me.ServiceDate.DefaultValue = Nz(DMax("ServiceDate","tbl_ServiceRecords", "EquipID = '" & Me.txtEquipID & "'"), Date())
End Sub

 

[Emma35]

It's just called txtEquipID. One odd thing i've noticed is that the brackets you have at the end of the code are never there when i open up the code window ? The very last one is there but the two before it (after the word Date) are always missing

 

[arnelgp]

Access automatically remove it.

 

[Dreamweaver]

Me.ServiceDate.DefaultValue = "#" & Nz(DMax("ServiceDate","tbl_ServiceRecords","[EquipID FK] = '" & Me.[EquipID FK] & "'"), Date()) & "#"

 

[The_Doc_Man]

the text box now displays the date 30/12/1899 ?

The reason you got that is because that is the Access Epoch date (reference date for the method Access uses to represent dates). So that means that something came back as 0.

If you can get to a code window you can use the menu View option to open up the Immediate Window. Do a Debug.Print of the expression to see what it returns.

Debug.Print DMax("ServiceDate","tbl_ServiceRecords","[EquipID FK] = '" & Me.[EquipID FK] & "'")

Or instead of using Me.[EquipID FK] you could provide an appropriate value. If you can't even get something back with Debug.Print then you have a problem in one of the field names in the DMax function. (Which would actually be consistent with the #name error.)

 

[Dreamweaver]

the text box now displays the date 30/12/1899 ?

That can also be because the value is not formated correctly which I had in another post a week or so ago I did this

Dim MyDate As Date
MyDate = #7/1/2019#
Me![Text13].DefaultValue = Format(MyDate, "\#mm\/dd\/yyyy\#")

Please note the above is set for US dates change it to \#dd\/mm\/yyyy\# for UK

 

[Emma35]

MickJav...i thought your suggestion had worked but then i tried to add a new record and the date in the Last Calibrated txt box wasn't correct. I think the best thing to do at this point is for me to strip the database and add leave the relevant forms for you guys to have a look at. I'll be about an hour or so...thanks again

 

[Dreamweaver]

Post a stipped down copy of the tables involved, we should be able to sort it for you
mick

 

[Dreamweaver]

Not to worry I'll create the tables from OP

 

[arnelgp]

on the subform's Current Event:

Private Sub Form_Current
Me.ServiceDate.DefaultValue = Format(Nz(DMax("ServiceDate", "tbl_ServiceRecords", "EquipID = '" & Me!EquipID & "'"), Date), "\#mm\/dd\/yyyy\#")
End Sub

 

[Gasman]

What is the date meant to be for the very first entry.?
Are you using an EquipID that alread exists?