getting a form field to default to the value entered in the last record

Here's a direct copy and paste from the actual code I'm using:

Me!txtPreparer.DefaultValue = "" & Me!txtPreparer & ""

That code works fine.

When I tried using

Me!txtPreparer.DefaultValue = """" & Me!txtPreparer & """"

I encountered the problem I described in my previous message. I thought
this might be a fluke because txtPreparer is actually a combo box that looks
up values in a table. So I tried the expression on text box field, and it
did the same thing. When I use the four double quotes on each side, it
literally puts double quotes around the values stored in both the form
control and the table field. So two quotes on each side work fine, but four
do not.

I also tried it in the Immediate window:

?"" & forms!frmReceipt_NoInvoice!txtPreparer & "" produces
John

But

?"""" & forms!frmReceipt_NoInvoice!txtPreparer & """" produces

"John"

in both the form field and the table.

I wonder why you're getting different results.

Paul

See comments inline:
--
Marsh
MVP [MS Access]


Paul James wrote:
Here's a direct copy and paste from the actual code I'm using:

Me!txtPreparer.DefaultValue = "" & Me!txtPreparer & ""

That code works fine.

Concatenating zero length strings onto a value only
guarantees that Access returns a string (not a number or
date). If txtPreparer is a Text field (string), then it was
already a string and the concatenations accomplish nothing.


When I tried using

Me!txtPreparer.DefaultValue = """" & Me!txtPreparer & """"

I encountered the problem I described in my previous message. I thought
this might be a fluke because txtPreparer is actually a combo box that looks
up values in a table. So I tried the expression on text box field, and it
did the same thing. When I use the four double quotes on each side, it
literally puts double quotes around the values stored in both the form
control and the table field. So two quotes on each side work fine, but four
do not.

Two quotes on either side doesn't accomplish anything beyond
what you would get by using:

Me!txtPreparer.DefaultValue = CStr(Me!txtPreparer)

The mystery is why concatenating a quote on each end is
being evaluated with the quotes still there. The only thing
I can think of that would produce the results you're seeing
is if the value of txtPreparer already has quotes around it.
I think I would like to know the **exact** value of the
txtPreparer combo box. Try using this and let's look at it:

Me!txtPreparer.DefaultValue = """" & Me!txtPreparer & """"
Debug.Print "V /" & Me!txtPreparer & "/"
Debug.Print "D /" & Me!txtPreparer.DefaultValue & "/"


I also tried it in the Immediate window:

?"" & forms!frmReceipt_NoInvoice!txtPreparer & "" produces
John

But

?"""" & forms!frmReceipt_NoInvoice!txtPreparer & """" produces

"John"

Yes, that's what I'm saying we want in the DefaultValue
property.


in both the form field and the table.

I wonder why you're getting different results.

Me too!

When I run these lines . .

Me!txtPreparer.DefaultValue = """" & Me!txtPreparer & """"
Debug.Print "V /" & Me!txtPreparer & "/"
Debug.Print "D /" & Me!txtPreparer.DefaultValue & "/"

in the After Update event of the txtPreparer combo box, I get this . . .

V /Smith, John/
D /"Smith, John"/

in the Immediate Window. And because I have this . . .

Me!txtPreparer = Me!txtPreparer.DefaultValue

in the Form Current event, VBA stores this . . .

"Smith, John"/

in the table fields. For the record that's current when I update
txtPreparer,

Smith, John

is stored in the table field. But as I move to other records,

"Smith, John"

appears in the table fields for those records.

The format property of txtPreparer in the form is blank, and Preparer is a
text field in the table.

Are you still getting different results than this?

Thanks for giving this issue your attention, Marsh.

Paul

There's a typo in the last post. In the text below, I wrote "Smith, John"
erroneously as "Smith, John"/ with a foreslash. It should have been written
"Smith, John" as below without the foreslash. Sorry for any confusion.

And because I have this . . .

Me!txtPreparer = Me!txtPreparer.DefaultValue

in the Form Current event, VBA stores this . . .

"Smith, John"

in the table fields.

Paul James wrote:

When I run these lines . .

Me!txtPreparer.DefaultValue = """" & Me!txtPreparer & """"
Debug.Print "V /" & Me!txtPreparer & "/"
Debug.Print "D /" & Me!txtPreparer.DefaultValue & "/"

in the After Update event of the txtPreparer combo box, I get this . . .

V /Smith, John/
D /"Smith, John"/

in the Immediate Window. And because I have this . . .

Me!txtPreparer = Me!txtPreparer.DefaultValue

in the Form Current event


AH HA! That's the thing that's causing all this trouble.

When you copy the DefaultValue (which is supposed to have
those quotes) to the value, you are copying the quotes too.
If you get rid of that statement in the form's Current event
and let Access supply the default value in the normal
fashion, you would not have the extra quotes.

I can't imagine a reason for you to have that line of code,
especially since it dirties the record before the user even
gets a chance to see it. Even worse, if you run that line
of code without checking for a new record, then that value
will overwrite any existing value in all records just by
navigating through a bunch of records.

--
Marsh
MVP [MS Access]

You're right. That was the problem.

Ok, I removed it, and it's working just fine.

Thanks again, Marsh.