Reuse data from previous record to populate same fields on form

[Bigdaddrock]

I have a form which contains both customer mail and billing addresses. In some cases the billing address is the same for several customers (ex their company is picking up the bill).
I must create record for each customer that contains their mail and billing address.
I would like to have the option to "autopopula te" the billing address based on the last Customer Record saved, if it is indeed the same billing address.
I am hoping that I can create a "setvalue" type macro (or VBA code, though I am not that well versed in it) to run upon clicking of a Command Button. The billing address is comprised of five different fields, which I would be taking from the last record saved before working on the latest record.
Any suggestions??
Thanks

[liimra]

?

If you have date field on the form (i.e when customer joined or created) then you can use SetValue using the Max function. If you don't, then you can just enter the first field and autopopulate the remaining using SetValue (all fields will be populated with the fields where the first field matches the field you have entered).

There is one function called DLast which should work as its name indicates Last but unfortunately it doesn't it is more a name because it returns a random record. There is a work around to get it working but seriously it is not practical at least as far as I know.

There is another way to populate the fields (form in hidden view) but I wouldn't state it as I don't recommend it.

Regards,
Ali

[Bigdaddrock]

I do indeed have a version of a date field. It is the "time last revised". As such, I could use that to find the last record input to the source table.
How do I structure the Set Value Exprression to find that source record from which I will copy the info in question?

[liimra]

Solution/

You define the criteria as the last date.
Attach the SetValue function to the Onclick event of the button. It will populate the field with the value with the last date (MAX).

FirstFieldName = DLookup("[FirstFieldName ]", "TableOrQueryName", "[DateFieldName] = #" & DMax("FirstFieldName", "TableOrQueryName") & "#")

SecondFieldName = DLookup("[SecondFieldName ]", "TableOrQueryName", "[DateFieldName] = #" & DMax("SecondFieldName ", "TableOrQueryName") & "#")

ThirdFieldName = DLookup("[ThirdFieldName ]", "TableOrQueryName", "[DateFieldName] = #" & DMax("ThirdFieldName ", "TableOrQueryName") & "#")

and so on for all other fields....
In your case you change the DateFieldName would be "time last revised". It is recommended not to use space with names. Apart from having to enclose them with square brackets you have to select it when copying or changing whereas if it is one word, you just need to double click on it.

Regards,
Ali

[Bigdaddrock]

Okay, I tried it but it failed. Let me provide you with actual table and field names and see how it should look:
I am seeking to populate BFNAME, BLNAME, BADDRESS, BCITY, BSTATE, BZIP based on the content of the same fields in the table "Register", for the record with the highest value in the field REGID.
I have tried replacing those values in your suggested code, but to no avail. By the way, I am trying to use the Macro SETVALUE. I am placing BFNAME in the Item box, and then for the expression, I am using your code beginning at the point "DLOOKUP... ..."
Thanks for your patience in assisting me.

[liimra]

//

Is the field "time last revised" in the same table?

Regards,
Ali

[Bigdaddrock]

Sorry about the confusion. I am using REGID instead of "time last revised" since the last REGID will have the greatest value (which means it was the last record saved). Yes, all of these appear in the same Table, REGISTER.

[liimra]

?

I see, and REGID happens to be Number Field, right? Note that dealing with Number Field is different than dealing with date field.

Regards,
Ali

[Bigdaddrock]

Yes, REGID is an autonumber.

[liimra]

Solution/

In this case you have to use different context.

DLookup("[FirstFieldName ]", "TableOrQueryName", "[REGID] = "& DMAX ("REGID","TableOrQueryName")

Regards,
Ali

[Bigdaddrock]

I am getting a message that it cannot parse the expression.
Here is what I put in:
DLookup("[BFNAME ]", "REGISTER", "[REGID] = "& DMAX ("REGID","REGIS TER")
Any suggestions as to what I am doing wrong?

[liimra]

You should close brackets!

DLookup("BFNAME", "REGISTER", "[REGID] = "& DMAX ("REGID","REGISTER"))

Regards,
Ali

[Bigdaddrock]

This seemed to satisfy the need for formatting the expression, so I could save the macro.
However, when I go to execute it I get a "Action failed" message box. When I then click Halt, I find that the BFNAME on the form has changed to become the last REGID number that is in the Register Table!!
At least something is happening. It appears that the macro is indeed looking into the Register Table and picking up the last record, now I just need it to select the BFNAME field. Any further suggestions?
Thanks for staying with me!

[Bigdaddrock]

Sorry, but I spoke too soon!!
It may be working. I will get right back to you!!

[liimra]

Very strange. You should be getting the BFNAME indeed as you are looking for its value in the table WHERE the condition (last record) is met. logically speaking, there is no reason for such behavior. I will try to create something similar just now and post it here.

Regards,
Ali