Append Query, or a better solution?

[andysgirl8800]

I need a little bit of advice on this one.
I have 2 tables that are used for different things, one table, denial data, is used for tracking all requests. It is updated with the form, Daily PAs. On the form are 2 buttons, each running a macro. One button exports the data to Excel by running a query to specify the date range. The second button is my problem. It also has a macro, with an append query, to append only requests that have been denied to my second table, Denials, to be later updated with additional information. The append isn't working because I am getting three different errors, a "type conversion failure," "key violations", "lock violations" and "validation rule violations." Now, I know I can begin working out these violations and get it working, but I'm sure that involves a lot of time and coding. I would really appreciate any other suggestions to accoplish the same tasks. I need to keep the approved requests seperate from the denied requests for auditing purposes. Thank you for your help.

 

[quest4]

There is nothing wrong with Append queries, I use them for archive old records. It is very import that the two tables are exactly the same, except for the table name, which can even be in a different dbase. You could use recordset or recordsetclone to also do the job for you, if you are good at VB coding. hth.

 

[andysgirl8800]

Thank you for the feedback, I appreciate it. Would you be able to post a sample of the code you use to do that? I can't download any of the DB.samples from this forum due to the firewalls on my PC, and I have been relying pretty heavily on pasted code examples. Also, any suggestions on how to get around the errors? I can post my code if that would help. I'm not a programming expert, and an intermediate with coding. Again, thanks for your help.

 

[quest4]

This is almost like archiving, so I would normally go wiht an append query. The best way to do it is copy the main and paste it with a different name. Then delete the data in the new table, it is ready to go and once you redo the appends query I think your problems will be gone. Oh, if you have a problem after the above, in the query make sure that you are not using *, make sure that all of the fields are listed. Append and delete queries can be touchie at times. You might want to try just turning your firewall off, somethime a right mouse menu option is disable. This is a pretty good site but there are two others you may want to joint, Utter Access and Tek-Tips. Oh, alot of my code also ties to functions/modules and with out see the whole thing, it might not make much sense to you. They are very large also, because of the code in them. If you like I can send you a small one with a couple of cute tricks in it, coding wise that is. Let me know on that one. hth.

 

[ScottGem]

I need a little bit of advice on this one.
I have 2 tables that are used for different things, one table, denial data, is used for tracking all requests. It is updated with the form, Daily PAs. On the form are 2 buttons, each running a macro. One button exports the data to Excel by running a query to specify the date range. The second button is my problem. It also has a macro, with an append query, to append only requests that have been denied to my second table, Denials, to be later updated with additional information. The append isn't working because I am getting three different errors, a "type conversion failure," "key violations", "lock violations" and "validation rule violations." Now, I know I can begin working out these violations and get it working, but I'm sure that involves a lot of time and coding. I would really appreciate any other suggestions to accoplish the same tasks. I need to keep the approved requests seperate from the denied requests for auditing purposes. Thank you for your help.

Yes there is a better solution. You really should NOT have data in multiple places. The lone exception to this is when archiving but I don't see this as an archival situation. There must be something in your record that indicates its a denial. What your button should do is append ONLY the primary key as a foreign key to your second table. This table should have ONLY the additional fields needed for info about the denial.

The Code behind the button is simple:

Dim strSQL As String

strSQL = "INSERT INTO tblDenials (RecordID) "
strSQL = strSQL & "VALUES(" & Me!txtRecordID & ");"
CurrentDB.Execute strSQL

Use the correct table name for tbDenials, the correct fieldname for the foreign key and the correct control name for txtRecordID whihc will be the one holding the PK for the main table.

 

[andysgirl8800]

I'm still mucking around with the Append Query, but keep getting hung up on the error messages. Not sure how to work around them.

On a completely different note, on the main form, frmDenial, everything is running smoothly with the buttons I've created to export data from certain fields into MSWord template letters with the use of bookmarks. The button will export the data into the template and print, just as it should, but the MSWord document will not close after completing this sequence. I've tried a few different variations of code to close the document, but it still remains open....here is a sample:

Private Sub Print_Letter_Click()
Dim objWord As Word.Application
'Start Microsoft Word 2000.
Set objWord = CreateObject("Word.Application")

With objWord
'Make the application visible.
.Visible = False

'Open the document.
.Documents.Open ("G:\Pharmacy\Prior Auth Docs and Data\Revised Pharmacy Denial Processes\KAN Not Nec or Benefit2.dot")

'Move to each bookmark and insert text from the form.
.ActiveDocument.Bookmarks("bmkFirstName").Select
.Selection.Text = (CStr(Forms!frmDenial!MBRFirst))

.ActiveDocument.Bookmarks("bmkLastName").Select
.Selection.Text = (CStr(Forms!frmDenial!MBRLast))

.ActiveDocument.Bookmarks("bmkHRN").Select
.Selection.Text = (CStr(Forms!frmDenial!MemberNumber))

End With

MergeButton_Err:
'If a field on the form is empty, remove the bookmark text, and
'continue.
If Err.Number = 94 Then
objWord.Selection.Text = ""
Resume Next

End If
objWord.Application.Options.PrintBackground = False
objWord.Application.ActiveDocument.PrintOut
objWord.Application.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
'Quit Microsoft Word and release the object variable.
objWord.Quit
Set objWord = Nothing
Exit Sub

End Sub



Any thoughts on this one? Thank you so much for all your help.

 

[andysgirl8800]

What your button should do is append ONLY the primary key as a foreign key to your second table. This table should have ONLY the additional fields needed for info about the denial.

Which table should have ONLY the additional fields needed for info about the denial? The first, smaller denial data table, or the second, larger Denials table to which I need the info appended to for further editing? Thanks.

 

[ScottGem]

Which table should have ONLY the additional fields needed for info about the denial? The first, smaller denial data table, or the second, larger Denials table to which I need the info appended to for further editing? Thanks.

The point is you should not have duplicate data or duplication in table strucutres. If you are entering data initially into one table, then that data should remain in that table. If you need to add information on selected records, then you mark those records in the table they were entered in and add a record to a related table with a foreign key to link the records. Then add your additional data in that table.