Opening form to view latest form (1 Viewer)

[daverskully]

Form: entryform

Fields:
RecordDate
Department
DepartmentNumber
7 text fields
6 memo


What I am looking to do is be able to click on a combo box for departments so that once a department is selected, the latest record for that department is populated in the form, however, it is opened as a NEW record so that when any changes are made to the information in the form to the text or memo fields, it is saved as a new record.

I'm having a lot of trouble with this.
Thank you very much in advance for your help.

 

[ajetrumpet]

skully,

This is very difficult to accomplish in a simple manner, as most of the suggestions I have seen involve dimming variables for each control on the form (within the current record), and then copying each one of them into a new record after navigating to it. However, I think I may have found a better solution for this type of problem, but the one thing you need have is a unique combination of values (or unique identifying number, like an autonumber) for each record. If you don't, you will wind up copying more than one record to the form (if more than one record exists for the department).

IF you can satisfy these requirements, the following solution will be much easier than messing around with variables:

on click of a button

dim x as string

  docmd.searchforrecord acdataform, me.name, acLast, _
    "[department] = forms!YouForm!DepartmentComboBoxName"
      x = me.DepartmentComboBoxName

docmd.setwarnings false

   docmd.runsql "INSERT INTO yourTable (field list here, do not include the unique identifier field, if there is one) " & _
      "SELECT yourTable.* (or the field list here, if you have to exclude the unique field or PK field from the list) FROM yourTable " & _
         "WHERE [unique field] = forms!YourForm!UniqueFieldControlName"

docmd.setwarnings true

DoCmd.SearchForRecord acdataForm, Me.name, acLast, "[department] = x"

This will navigate you back to the record that was just appended to the table, so you can continue to edit it, if necessary. It navigates to the correct record because the append process adds the copied record to a point in the table after the record that you copied from, making it the new last record for the department you are working with.

This does work, and it's much faster than declaring variables and looping through them...

 

[daverskully]

Wow thank you for your incredibly fast response ajetrumpet, and also thanks for the detailed answer, I still have a question though, what do you mean by the following, I'm not sure what I'm suppose to fill these in as (if anything):
UniqueFieldControlName
acDataForm
Me.Name

When seperating the items in the field list, should I use a comma?
and I forgot to mention that I do have a unique id field called ID, which is an autonumber.

Thanks again
Dave

 

[ajetrumpet]

I'm not sure what I'm suppose to fill these in as (if anything):
UniqueFieldControlName <---- The control name on your form representing the field that contains the unique identifying value for the record
acDataForm <--- This is just part of the syntax of AC command. Look it up in help if you'd like.
Me.Name <---- This is also part of the syntax of the AC command (where you indicate the name of the form)

When seperating the items in the field list, should I use a comma?

Yes, just like you do for an APPEND query that is written in the SQL window. If you don't remember, write and APPEND query with the query wizard and memorize the syntax of it.

and I forgot to mention that I do have a unique id field called ID, which is an autonumber.

OK, then write down any field you want to include, except the autonumber.

And BTW, since you are appending data here, having a PK in your field will probably result in a key violation if you try to append a record to the same table in which it came. If the autonumber of yours is actually the PK, this method is probably not going to work. You may have to append the data to a temporary table, and then copy it from there back to your original table.

That would be the only problem with this method (sorry, forgot to mention the key violation possibility - each PK has to be unique, remember).

 

[daverskully]

Thanks again for the awesome response Adam. Yes, my autonumber is the PK, what are my options for still getting this script to work properly? You said that I have to append the data to a temporary table then copy it from there back to the original. Can you possibly give more detail on how I would go about doing that?

Wouldn't a new autonumber just come up for the new record if I don't include the autonumber (ID field) as one of the fields that will be copied?

 

[daverskully]

Ohh and I'm having trouble with the DoCmd.SearchForRecord .... it isn't being recognized. I have access 2000, is this why?

 

[daverskully]

What about this solution, which may just be an interpretation of the last solution you provided..

Have one form, called EntryForm, where the user can select a department from a combo box, then press a button that goes to the latest record for that department. Once this is done and the latest entered record is present in the EntryForm, there is another button created on this form which then opens another form called PopUP (linked to another table?), which copies/duplicates/appends? the information from the other form so that a new record is created with the copied information present. I see what you're saying regarding the ID (Autonumber) field Adam. If the information is copied to the other form, all fields except for the autonumber would have to be transfered right, because this would create a key violation?

I think I understand how it can be done and I understand what you explained, the problem is that I don't really know how to implement it, I'm not very experienced at all with scripting/coding.

Any help that you, or anyone else, could provide would be more than greatly appreciated.
Thanks again, I'll likely respond very quickly to new posts

 

[daverskully]

Alex, I've found a solution. Thanks for your help. Incase anyone has a similar problem I'll list what I did as a solution. I created a form by itself with a combo box listing the departments based on a departments table. Once a department is selected, I had a macro on the combo box which used a query as a filter for the selection made in the combo box. The macro opened a new form (form linked to a table named Data which contains all the information that the user will enter, contains an autonumber key) based on the query and opened the last record for the department that was selected. Then I included a button on the opened form and assigned it to duplicate. Once clicked, it makes a copy of the filtered record that is showing and can now be saved as a new record. I'm sure there are many many ways to retrieve old records like this, but I find this method does the job pretty well. Thanks again for your help!