469,350 Members | 1,757 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,350 developers. It's quick & easy.

Reuse data from previous record to populate same fields on form

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 "autopopulate" 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
Sep 18 '10 #1

✓ answered by liimra

You should close brackets!

Expand|Select|Wrap|Line Numbers
  1. DLookup("BFNAME", "REGISTER", "[REGID] = "& DMAX ("REGID","REGISTER"))
Regards,
Ali

25 6617
liimra
119 100+
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
Sep 18 '10 #2
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?
Sep 19 '10 #3
liimra
119 100+
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).

Expand|Select|Wrap|Line Numbers
  1. FirstFieldName = DLookup("[FirstFieldName ]", "TableOrQueryName", "[DateFieldName] = #" & DMax("FirstFieldName", "TableOrQueryName") & "#")
  2.  
  3. SecondFieldName = DLookup("[SecondFieldName ]", "TableOrQueryName", "[DateFieldName] = #" & DMax("SecondFieldName ", "TableOrQueryName") & "#")
  4.  
  5. ThirdFieldName = DLookup("[ThirdFieldName ]", "TableOrQueryName", "[DateFieldName] = #" & DMax("ThirdFieldName ", "TableOrQueryName") & "#")
  6.  
  7.  
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
Sep 19 '10 #4
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.
Sep 20 '10 #5
liimra
119 100+
Is the field "time last revised" in the same table?

Regards,
Ali
Sep 20 '10 #6
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.
Sep 20 '10 #7
liimra
119 100+
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
Sep 20 '10 #8
Yes, REGID is an autonumber.
Sep 20 '10 #9
liimra
119 100+
In this case you have to use different context.

Expand|Select|Wrap|Line Numbers
  1. DLookup("[FirstFieldName ]", "TableOrQueryName", "[REGID] = "& DMAX ("REGID","TableOrQueryName")

Regards,
Ali
Sep 20 '10 #10
I am getting a message that it cannot parse the expression.
Here is what I put in:
DLookup("[BFNAME ]", "REGISTER", "[REGID] = "& DMAX ("REGID","REGISTER")
Any suggestions as to what I am doing wrong?
Sep 20 '10 #11
liimra
119 100+
You should close brackets!

Expand|Select|Wrap|Line Numbers
  1. DLookup("BFNAME", "REGISTER", "[REGID] = "& DMAX ("REGID","REGISTER"))
Regards,
Ali
Sep 20 '10 #12
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!
Sep 20 '10 #13
Sorry, but I spoke too soon!!
It may be working. I will get right back to you!!
Sep 20 '10 #14
liimra
119 100+
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
Sep 20 '10 #15
liimra
119 100+
Alright

Regards,
Ali
Sep 20 '10 #16
It works!!! Thank you for all your patience!!
I really appreciate it!!!
Sep 20 '10 #17
liimra
119 100+
You are most welcome. Glad it worked.

Regards,
Ali
Sep 20 '10 #18
Hope you don't mind but I am having a problem doing a similar Macro, but am getting a syntax error.
Here is the Expression:
DLookUp("INVNUM","INVOICE","[ LatestRevisionDate] = " & DMax("LatestRevisionDate","INVOICE"))
The only difference from the successful (thanks to you) expression:
DLookUp("BFNAME","REGISTER","[REGID] = " & DMax("REGID","REGISTER"))
Is that REGID is a number, where LatestRevisionDate is a Date/Time DataType with a General Date format.
Any suggestions as to how I might alter my expression?
Thanks
Sep 22 '10 #19
liimra
119 100+
Of course I don't - You are most welcome. Yes as stated before you need different context so it becomes:


Expand|Select|Wrap|Line Numbers
  1. DLookUp("INVNUM","INVOICE","[LatestRevisionDate] = #" & DMax("LatestRevisionDate", "INVOICE") & "#"))


Regards,
Ali
Sep 23 '10 #20
Unfortunately, I once again got a syntax error. Any further suggestions?
Sep 23 '10 #21
colintis
255 100+
Did you changed anything and get the syntax error? or it just suddenly pops the error with no reasons?
Sep 24 '10 #22
No changes to my original input. I copied and pasted your suggestion and got that response.
Sep 24 '10 #23
colintis
255 100+
Sorry I'm not liimra whose giving you the code. :)

Is the date format the same as recordset you are getting from? Error may occurs if the date format are different. So just in case the you can add format() to the code.
Expand|Select|Wrap|Line Numbers
  1. DLookUp("INVNUM","INVOICE","[LatestRevisionDate] = #" & Format(DMax("LatestRevisionDate", "INVOICE"), "mm/dd/yyyy") & "#"))
Just double check the formatting of the source, and change the "mm/dd/yyyy" to the once it matches.
Sep 24 '10 #24
To reuse last input could you not just assign the value of entered fields to variables when you exit that control, then have a command button which when clicked cycles through controls assigning saved variables? each time the form is filled in you get a new set of variables and simply clicking command button on next form entry populates form with valuse from previous entry?
Sep 24 '10 #25
There is only one "LatestRevisionDate" involved in the following expression that you suggested.

1. DLookUp("INVNUM","INVOICE","[LatestRevisionDate] = #" & DMax("LatestRevisionDate", "INVOICE") & "#"))

Remember, I am seeking the INVNUM associated with the greatest value of the "LatestRevisionDate" in the INVOICE Table.
Is it possible that the DMax function will not work with a record that is in the General Date format? If not, what similar function is available to do the same thing? Perhaps something like DLAST??
Thanks for your ongoing assistance.
Sep 24 '10 #26

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.