CODEWITHSUNDEEP
vbams-access
AccessProgrammer
AccessProgrammer

Reputation: 11

Set default value of text box to query result vba

I found this article Set Default Value of Text Box to Query Result I'm trying to use the following code, but I keep getting an error. The name of the query is MaxNote and then Field retured in the query is MaxNote.

Public Function MaxNote()
       MaxNote = CurrentDb.OpenRecordset("MaxNote").Fields("MaxNote")
End Function

Upvotes: 0

Views: 354

Answers (3)

ComputerVersteher
ComputerVersteher

Reputation: 2696

Here anOn Error Resume Nextcan be helpfull as an error indicates an empty result.

Public Function MaxNote() as Variant
       MaxNote = "" ' default return value for not records found. Can be NULL or 0 too
   On Error Resume Next ' if lookup fails code resumes next line, what is End Function (nothing happens)
       MaxNote = CurrentDb.OpenRecordset("MaxNote").Fields("MaxNote") ' fetch the field from first row of recordset (standard after .OpenRecordSet() ), if no results an error is raised, but function resumes next line of code, what ends the function and still with MaxNote = "", the value if no records are found
End Function

Upvotes: 0

Gustav
Gustav

Reputation: 55841

Use DLookup for such simple tasks, and note, that DefaultValue is text, and use Nz if MaxNote can be Null:

Me!YourTextbox.DefaultValue = Chr(34) & LTrim(Nz(Str(DLookup("MaxNote", "MaxNote")))) & Chr(34)

Upvotes: 1

Van Ng
Van Ng

Reputation: 803

Public Function MaxNote()
    Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset("MaxNote") 
    rst.MoveFirst
    MaxNote = rst.Fields("MaxNote")
End Function

Try this, if you prefer rst opening, instead of DLookup

Upvotes: 0

Related Questions