Find Newest Record, Close Word & Not In List

[andysgirl8800]

I've almost completed the DB I'm working on, but still have a few loose ends to tie up that I can't seem to figure out. I've spent many hours already on this forum searching for solutions, and have tried a few different things, but I still can't work out these bugs.

First, I need my form to open with the latest records filtered first, so that the most recently added records will be easiest to find and edit. I'm not sure how exactly to do this. I've tried a run query when the form is opened, but I still need all the records displayed or accessible. The form is called frmDenial, and I would like it to be looking in the DateLogged field of the form. Any suggestions?

Second, I have command buttons that will export data in the fields to MSWord Templates via bookmarks. The button runs well by opening the document, inserting the data, and printing. I have also tried several different code syntax to close word once it is done, but it is still staying open after printing. Here is a sample of the code:
________________________________________
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))
.ActiveDocument.Bookmarks("bmkAddress1").Select
.Selection.Text = (CStr(Forms!frmDenial!MBRAddress1))
End With
Print_Letter_Click_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
_______________________________________

Finally, I have two cascading combo boxes set up that will auto pop related fields based on the selection made. But, if an item is not in the list, I would like the user to add it to the linked table to appear in the list. I also have this working well, with a pop up asking the user if they want to make the addition, type in the new item, and add to the list without requiring the user to refresh or exit then re-enter the form. But it will only add the item name, and not the item description (another field in the form and another column in the table). How can I modify the code to prompt the user to enter these other details? I can link it to a pop up sub form to enter the data, but if possible, would rather the boxes pop up to have the user type in the data. Here is the code I have so far in the NotInList Event...
__________________________________________
Private Sub DrugName_NotInList(NewData As String, Response As Integer)
Dim DB As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String
strMsg = "'" & NewData & "' is not an available Drug" & vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to add the new Drug to the current Database?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to add or No to re-type it."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new model?") = vbNo Then
Response = acDataErrContinue
Else
Set DB = CurrentDb
Set rs = DB.OpenRecordset("tblDrug", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!Drug = NewData
rs.Update
If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
rs.Close
Set rs = Nothing
Set DB = Nothing
End If
End Sub
______________________________________________
The two other colums in the tblDrug that I need the user to be prompted to fill are Denial Reason (column 3) and Alternative (column 4). What would be the best way to accomplish this?

I would really appreciate any help or suggestions with any of these problems. Thank you so much!

 

[RuralGuy]

Without knowing more about your forms and tables, my thoughts on the first question are: sort the records descending by date.

That is not the first time I've seen the second question so googling should turn up something.

As far as the last question goes you should really pop out to another form that will accept the information for all of the fields you need for this table and save it with something like:

' Display form to collect data needed for the new record
DoCmd.OpenForm "AddNewStuff", acNormal, , , acAdd, acDialog, NewData

 

[andysgirl8800]

Thank you for your thoughful suggestions. I currently do have the form sorting by date, thanks, that helped with that problem.

As for the MSWord issue, I've already searched this forum for hours and have tried the different coding options, but my document will not close, no matter what code I put in there. I was hoping there was just a simple error in my syntax that will resolve the issue.

Finally, as for the not in list function, how do I change my existing code to run the other form, input the data, save in the table, refresh, and return to the main form with as little user interface as possible? Basically, I am designing this DB to be as "idiot-proof" as possible, per my boss's instructions.

The main form is frmDenial.
The sub form is frmNewDrug
The combo box on the main form is DrugName
The underlying table for this combo is tblDrug

Again, thank you for your help.

 

[RuralGuy]

Hi,

Here's a modified version of your NotInList code:

--- Warning, untested air code ---
Private Sub DrugName_NotInList(NewData As String, Response As Integer)

On Error GoTo DrugName_NotInList_Err

Dim strMsg As String
strMsg = "The drug [" & NewData & "] is not currently an available Drug!" & _
vbCrLf & vbCrLf & _
"Do you want to add this Drug to the current Database?" & _
vbCrLf & vbCrLf & _
"Click Yes to add or No to re-type it."

If MsgBox(strMsg, _
vbQuestion + vbYesNo + vbDefaultButton2, _
"Add new model?") = vbNo Then
Response = acDataErrContinue
Else

' Display form to collect data needed for the new record
DoCmd.OpenForm "frmNewDrug", acNormal, , , acAdd, acDialog, NewData
Response = acDataErrAdded
End If

DrugName_NotInList_Exit:
Exit Sub

DrugName_NotInList_Err:
With Err
MsgBox "Error: " & .Number & vbCrLf & .Description, _
vbCritical Or vbOKOnly, .Source
End With
Resume DrugName_NotInList_Exit

End Sub

Idiot proof huh? That means you can't let humans close to it! <g>

You need a means of maintaining the tblDrug table. Adding, editing and deleting entries in this table. You need Referential Integritity turned on and relationships defined for each of the tables where tblDrug is used. You are using an autonumber as the DrugID and *only* putting the DrugID number in the other tables, right?

As to the Word problem, MVP Albert D. Kallal has a Merge mdb that might give you some ideas.

http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html
It is called "Super Easy Word Merge"

 

[andysgirl8800]

Thank you so much for the modified notinlist event. That's perfect! Mucho Kudos!! I've been struggling with that one for quite some time, even though it must have had a simple solution.

 

[andysgirl8800]

Also,
I tried the mail merge options and have viewed this link before. Tried to incorporate it into my DB and had nothing but nightmares and couldn't find the right solutions in this forum, and unfortunately was also getting no help from other users, except "search the forum". I was about to give up until I went the bookmark route. Now all is well and it is merging beautifully, except that one little bit of not closing word. I'm sure there must be something small (again in my code) that is either conflicting and confusing Word or the DB, or it's not recognising the request to close. I would hate to have to convert it all to merge again after working so hard on the bookmarks. I'd rather deal with manually closing word! So now I'm searching/tweaking/asking/pleading for assistance with this silly little function! I've used this forum almost exclusively to accomplish everything so far, and would LOVE to move this project off my desk once and for all!

 

[RuralGuy]

Hi andysgirl8800,

I'm glad it worked for you. Lots of success on the rest of your project.

 

[RuralGuy]

Hi andysgirl8800,

Have you seen this MS article?

http://support.microsoft.com/?kbid=210129
ACC2000: Applications Run from Automation Do Not Always Close