Assign default values from the last record

[trinismooth]

(Before I post I must say that this Forum has assisted me greatly in solving many if not all of my problems and for that I am very grateful)


At present I have a form that has 4 sub forms within, the purpose of the form is to store customer’s information and their associated salary deductions. Eg. The main form will have customer name, account number, payroll number etc, and sub 1 form will allow you to select what loans he/she is paying and also how much, while another will allow you to select what savings account he/she is contributing to and how much etc. What I want is that once the form is filled out for the customer and saved the next person who is attempting to add another form for the same customer will be presented with the records for the last one entered as to speed up data entry.

[beacon]

I think you can setup your form's OnLoad() event to populate each field with data from your query by using SQL.

The only thing I would do is make sure that the query your form is using as a record source has an autonumber/ID field:

'declare a variable for the controls (textboxes, etc.) on your form
Dim strSQLControl1, strSQLControl2, strSQLControl3
 
'set the first variable equal to the first record on the query when you sort descending, which is the opposite of the true 1st record (if you don't put descending it will select the true 1st record)
strSQLControl1 = "SELECT Top 1 [YourField for Control 1] FROM [YourQuery] ORDER BY [the autonumber/ID field] DESC"
 
Me.Control1 = strSQLControl1
 
'repeat for the other controls as necessary

I haven't tried this out, so I'm not 100% sure it works, but I think it's close.

If you want to give the user the choice to fill in the fields with default information, create a command button and put the above code in the OnClick() event instead.

[trinismooth]

Thanks, but will it work as I want the last records specific to that customer selected be available. So if the user select John Doe, the last record entered for john doe will populate

[Dan2kx]

i would suggest the bookmark functionality, specifically RecordSetClone
you can use the wizard to ceate a button that navigtes to records (and creates the code) then nick it and put it where you need, or follow clicky.

[beacon]

Thanks, but will it work as I want the last records specific to that customer selected be available. So if the user select John Doe, the last record entered for john doe will populate

I think you can add the WHERE clause to your SQL statement, but to do so in the OnLoad() event would mean that you would probably have to select a customer from another form and then pass the customer as a parameter to the current form.

I guess it's all dependent on how you actually select the customer. If the customer is chosen on the current form (say from a dropdown), you could add the SQL statement to the AfterUpdate() event for the Customer combo box.

If someone else has a better way, you might want to try that instead since, if you have a lot of info that needs to be filled in, this method could potentially be heavy on the code and might take a while to write.

I'm not really familiar with the bookmark method, although I have seen it used to filter a form. If I remember correctly, I think there's a different article than what Dan suggested Microsoft's support website that gives examples of searching a recordset and bookmarking a record, although it may be the same one as in the msdn.

[FishVal]

Repeating values for a field in several records

[NeoPa]

I suggest you may have to control the setting of your defaults by the changing of the record in your main form. In other words the code to set the defaults would have to reside in the Current event of your main form, even though the defaults are being set on one of your sub-forms.