Need to copy select fields from a prior record

[keun]

I have this database which tracks projects for different customers. Customers will have multiple projects tracked, and each will have a unique project number. However, it is likely that a later project will have the same specs as a previous project. So, what I would like to do is give the user the ability to open a pop-up form which lists the projects already entered for the particular customer. Once the project number is selected, the fields (which I choose) will be populated.

I can imagine the concept - an update query, I just do not know how to retrieve the projects and pass the selection through to the query.

 

[Remou]

Part 1

If you wish to populate the fields of an existing record when a user selects an item in a combo or listbox, it is usually best to store the data in the combo or listbox, there is no need for the user to see the data, column widths can be set to zero. The code would run something like this:

Forms!MainForm.SomeField = Me.lstProjects.Column(5)

Where column numbering starts from zero.

http://lessthandot.com

 

[Remou]

Part 2

If you wish to add a new record when an item is selected, an append query is the way to go. If the project number is an autonumber, that is about it, if not, you will need to get a new project number from the user. The pop-up form is probably the best place for this.

INSERT INTO tblProjects ( ID, SomeField )
SELECT Forms!PopUp!ProjectNumber, tblProjects.SomeField
FROM tblProjects 
WHERE ID = Forms!PopUp!lstProjects

http://lessthandot.com

 

[keun]

Thanks for the help.

However, I am not using a combobox, and I am not making a new record. The record exists, because it is created in a different form - a form where I can more tightly control the information gathered.

I will also like to add - I do not need the user to select the record for copying from, I can just use the previous query record. NOT table record, so I cannot use autonumber.

Once the record is created (the project started), the user goes back to the entry form. Because my query sorts on ProjectName, and because ProjectNames are named such that they sort properly, users can grab duplicate fields from the previous record.

I asked a similar question a while ago, and was instructed to use PrevRecVal to get info one field at a time. That works in this case too, but I want to grab data from like ten different fields.