how to set a function for test

[JoeyB_99]

I am using Access 2007.

I would like to create a function in a module for a text field, similar to the function created by Bob Larson on 10/12/2012 when answering a thread called "Default Value in Forms based on value in another table". It appears that he is creating a function for a numeric field.

I have a situation where I want to establish a function on a text field.

Also, is there a different way to do this without using the Lookup function? I have found that the Lookup function can slow down my database.

Any input/comments/suggestion would be greatly appreciated.

 

[DavidAtWork]

can you give a bit more detail or example of what the text field might hold and what you want to do with this text value
David

 

[jdraw]

Create a function in a Standard Module (not form, report, or class module) like this:

Function GetDefaultTID() As Long
   GetDefaultTID = Nz(DLookup("YourFieldNameWhichHasTheSetting", "YourSettingsTableHere"), 0)
End Function

set the DEFAULT VALUE property for the control in the form to

=GetDefaultTID()

This is the function and usage that Bob mentioned.

What specifically are you trying to do?

 

[JoeyB_99]

Thank you DavidAtWork and jdraw, for your interest and offer to help.

The field I want to use in this situation is a text field from a table. I want to use it as a default value in a control which is bound to a field in a different table.

The user can accept this default value when creating a record or they can over-ride the contents of this control.

I am fairly new/green with VBA so please be patient.

Also, please be specific (i.e. which property) if it may be better to not use a standard module approach.

 

[jdraw]

Please identify the field and table involved.

 

[JoeyB_99]

The control is bound to tblAmountsBilled.DescriptionOfWork.

I want the default value for this control to come from the table tblCostCodeReferenceData and the name of the field here is CostCodeDescription.

Something else I forgot to mention. The records on this form are done through the selection of a Cost Code using a combo box.

I have tried using simple default value assignment type VBA code in the On Click property of the control for the combo box.

 

[jdraw]

Based on Bob's post

Create a function in a Standard Module:

Function GetDefaultDesc() As String
GetDefaultDesc = Nz(DLookup("CostCodeDescription", "tblCostCodeReferenceData"), " ")
End Function


set the DEFAULT VALUE property for the control in the form to

=GetDefaultDesc()

 

[JoeyB_99]

Thanks jdraw but its not quite right.
　
No matter what record I select from the combo box it returns the default text value from the first record of the table called tblCostCodeReferenceData.
　
So I guess I will need something in the criteria for the Dlookup function that connects the proper description with the record that is being selected.
　
The control for the combo box is called Combo54, inside the main form called [Cost Code Select].
　
I am selecting records based on a field called Cost Code.
　
The control of the field that I am assigning the default value to is in a subform called [Amounts Billed subform].
　
The Master/Child link between the main form and subform is by the field Cost Code.
　
Any help you can provide with writing the criteria would be greatly appreciated.​

 

[jdraw]

I wondered about the third parameter of the DLookup as well --there was no third parameter in Bob's post.

Try this line (it's saying where the costcode in the table = the costcode on the form)

GetDefaultDesc = Nz(DLookup("CostCodeDescription", "tblCostCodeReferenceData", "CostCode =" & me.costcode), " ")


Note: I just got the incoming post at 6:30PM and it was recorded at 8:43 AM????

 

[JoeyB_99]

Thanks Jdraw but it doesn't work.

When I compile the code I get an error message "Invalid use of Me keyword".

 

[jdraw]

Joey, I'm guessing where all this is being used.
Can you explain/describe the situation? The Me refers to the current form, and I guessed you had a field called costcode on the form, but that may be incorrect.

As I reread some material I see Cost Code so when you include a space in a field or object name you have to enclose that name in square brackets.

So costcode becomes [cost code]. You can try using [cost code].

Can you post a copy of your database (no confidential/personal info)?

 

[JoeyB_99]

Thanks again jdraw, for sticking with me.

Things are getting confusing for me too! If you check Bob Larson's post you will see that I am entering code into a standard module, as opposed to an event procedure. Does that make any difference here?

Yes, there is a field on the main form called Cost Code, and there also is a field in the subform called Cost Code because thats how the two forms are linked.

Yes, I am using square brackets because of the field naming. I inherited this database so I have to make such necessary adjustments.

It seems that no matter what I do with the field name I keep getting the ME error message when I compile the code. Why is that?

I'm not sure about the posting of my database due to certain confidential information. I will have to think about option.

What else can I try here?

 

[jdraw]

Let's try getting very specific with the control on the form

Try
GetDefaultDesc = Nz(DLookup("CostCodeDescription", "tblCostCodeReferenceData", "CostCode =" & Forms![Cost Code Select].[cost code]), " ")

Is cost code a number or string?

 

[JoeyB_99]

Okay jdraw, I think we might be making progress.

Cost Code is a string/text.

When I compile the revised code as per your last post I do not get an error message. But when I open the form I get a VBA error message, run-time error 3075.

"Syntax error in number in query expression '[Cost Code]=00000'."

It is providing the very first cost code in the table called tblCostCodeReferenceData.

But the weird thing here is that the cost code string/field is 6 characters in length. The error message is truncating the last character.

Should I be adding the single quotes somewhere in my criteria expression?

 

[jdraw]

Yes, it seems so since you have a string

GetDefaultDesc = Nz(DLookup("CostCodeDescription", "tblCostCodeReferenceData", "CostCode ='" & Forms![Cost Code Select].costcode) & "'", " ")

 

[JoeyB_99]

Success! Thank you jdraw that did it! It is working fine. I have tested it with different record selections (cost codes) and the output is as desired!

Thanks again jdraw.

I have another VBA coding quirky situation so I will post it separately.

 

[jdraw]

Glad you have it resolved.