Beginner seeking solution to specific task of moving records from tables.

[thisisadhoc]

Hello,

My current task using Microsoft Access 2007, a program that I have never used, is to create 2 tables, (currently named MQL and DNA (will stay as acronyms for sensitivity reasons)), and then once certain criteria is met, (criteria being a certain situation, nothing to actually do with numbers or orders placed), is to move a record, (name and other information), from 'MQL' to 'DNA' while deleting that record from 'MQL, whilst generating an object that appears as a letter that will be filled in with the information found in the moved record. I know it sounds complicated, but it looks really simple in my head, I just don't know what I am really doing, so it may be much more difficult than it sounds. The only reason I am using Access and trying to accomplish this is because my supervisor wants it done this way, and to make it easy for any user to generate this task of moving someone from one roster to another while generating a letter with a click of a button; I am guessing it is out of pure laziness, but I have learned not to ask too many questions in my line of work.



Basically the process I have in my head is this:

1. MQL Roster has 200 names, it is pretty much a constant with a little editing here and there.

2. Something happens, and an individual needs to be moved from the MQL Roster to the DNA Roster, (which contains less than 5 names usually).

3. I bring up the Database and the Switchboard greets me. I click on the 'Start DNA procedure' button.

4. That button brings me to a SOMETHING where I can search for the individual's name.

5. Once I find that individual's record, I click another button labeled 'DNA.'

6. When I click it, the record is moved, (as in copies, then deletes the original), from the 'MQL Roster' to the 'DNA Roster', and it generates an object that appears to be a letter, where there is a main document already stored, and this process fills out the specific information. (I have found that the 'Mail Merge' function used with Microsoft Word works well, but I need it to generate only one letter that is printable. It doesn't have to be an actual letter, just something containing the main document information, that fills out the specific fields labeled and makes it printable.)

Again, my supervisor wants all of 'step 6' to happen with virtually one click of a button, or at least something that is incredibly easy to use to anyone without any knowledge of Access.

Sorry for the length, but I have been trying for days and days to figure out how to make this work, but I do not have any knowledge of programming, which may be the only way to make this work... if it IS even possible. Thank you for your time and I appreciate any help you can offer!

[Marcotte]

Is there any relationship between the two lists/tables? Or is DNA simply a copy of a subset of records from MQL, with all the same fields?

If it's simply a subset copy, then this may be easier to do in Excel with a macro or two. Here's how I would do it.

Make a tab for each table, DNA and MQL. Each tab would have the same column headers. DNA would initially be blank, and MQL would have all your records. A third tab would be your user interface tab and consist of one or two buttons. The second button could run an editing macro if you wanted to get fancy - the alternative would be to allow the user to manually edit the MQL tab of the spreadsheet. This function is probably easier to do in Access with a Form, but I'm not sure. The other button would be the "Start DNA Procedure" button.

You'd also add a ComboBox control that would be linked to your list of names. The user would pick the person to be copied from this drop down list. The "Start" button would run a macro which would read the value of this ComboBox, select that row from the MQL sheet, copy it, paste it to the DNA sheet, then delete the row from the MQL sheet. Finally it would kick off a mail merge for that name.

You may need to add another button do clear the DNA sheet of data if that is required.

[Robeen]

Let me start out by saying that this is definitely possible.
It may be a little complicated for a learner but that is only because of the learning curve.

I have not worked much with Switchboards - so I'll just use a simple 'Startup' Form.

1. Create a Search Form with which to find your individual.
2. Put a Combo Box on the Form and follow the Wizard to bind the combo box to the Name field of the MQL Table.
3. Run the Form to make sure it is showing you the complete list of individuals from the MQL Table.
4. Put a command button on the Form but cancel out of the Command Button Wizard - you can code this button later.
5. Create a 'Startup' Form and put a Command Button on it and Name it 'cmdStartDNA'. You can Label it 'Start DNA Procedure'.
6. If you get a Command Button Wizard, you can tell it you want the button to Open the Search Form when clicked.
7. Create a Report that has all the stuff you want printed each time you run the DNA procedure.

Get this done and working before you start putting code in the command button on the Search Form.

The Command Button on the Search Form should do this:
1. Find the row of data in the MQL Table that corresponds to the Name you selected on the Form.
2. Insert that row of data into the DNA Table.
3. Delete the row of data from the MQL Table.
4. Print the Report.

Post back here if you need help.

[John_G]

Hi -

This can all be done with one click!

Moving a record from one table to another is the result of two operations - an insert into one table, followed by a delete from the original one.

The insert would look something like this:

INSERT INTO DNA SELECT * FROM MQL WHERE criteria

criteria is whatever information is required to uniquely identify the record, e.g. Employee_ID, Patient_ID, etc.

If you are working from a form, where you select a record, the click a "Move" button, your code might look something like this (in the on_Click event of the button):

Dim SQL as string
SQL = "INSERT INTO DNA SELECT * FROM MQL WHERE Person_ID = " & me![Person_ID]
CurrentDB.Execure SQL, dbfailonerror

Is your "letter" laid out in a format such that it could be impletemented as an MS Access report? If so, then all you need to do is design the blank report you need, using the table MQL as the record source, then print the report for only the one record you need:

DoCmd.OpenReport "Report1", acPreview, , Person_ID = " & me![Person_ID]

This previews the report on the screen; if you want to print without previewing first, change acPreview to acNormal.

To delete the current record of the form, use

docmd.runcommand acCmdDeleteRecord

So, now you have an OnClick procedure like this:


Dim SQL as string
'
' Copy the record from MQL to DNA
'
SQL = "INSERT INTO DNA SELECT * FROM MQL WHERE Person_ID = " & me![Person_ID]
CurrentDB.Execure SQL, dbfailonerror
'
' Generate the report
'
DoCmd.OpenReport "Report1", acPreview, , Person_ID = " & me![Person_ID]
'
' Delete the record and requery the form
'
'
docmd.setwarnings False ' Turns off the confirm delete message
docmd.runcommand acCmdDeleteRecord
me.requery
docmd.setwarnings True ' Turns the confirm delete message back on

Do you need assistance with the "something" you referred to to find the records? The combo box wizard may be what you need.

John

[recyan]

but I have learned not to ask too many questions in my line of work.

Apart from this very valid reason
Just out of curiosity, was wondering, why not do this in the same table,
say we have 2 additional columns - "MQL_DNA" & "DateDNAUpdatedON",
with "MQL_DNA"having default value of MQL
and when you find a particular record that you want to DNA, update the "MQL_DNA" & "DateDNAUpdatedON" fields to "DNA" & the "Date of Updation"
or
Am I missing something totally?

Thanks