Any MS Access experts here?

Megadeth4168

We have a 20 years old records management system in one of our departments at work.

This department is getting new computers and we have come to find out that this old records system does not work on Windows XP. So I am going to create an access rms system to emulate their old system as best as I can.

I do have quite a bit of experience with access and this normally would be no problem with me but then they told me that one of their requirements is a section for notes....

Unfortunately they need the ability to write several pages of notes... Basically 20 pages aren’t even going to cut it. I have already ruled out the use of a memo field because they would need at least 2 memo fields and because anytime I work with memo fields on that scale the system seems to become unstable.

What I was hoping to do is create some kind of command that will open a word document (under the same name as the ID number of the record) and include the notes there...

So what I would need is a link on every record that corresponds to a specific set of notes from an external; program like word... I might actually need a couple of these per record because sometimes more than one person writes notes.

I will need to make these notes associate with the records for searching purposes ect...

I haven’t started on this project yet however I already know this will take me some time to figure out so that is why I am asking now.

Oh... One more detail... On the old system they would always end up copying and pasting the notes into word anyway because word was better formatted for their needs ect.

Any suggestions are appreciated.

Thanks

TheShadow

Scary project. CTREE was great with text databases and 20 years ago sounds about right when people were doing those things. While you are pondering, do you know why it won't work under XP. If it is DOS based I have managed to get a lot of old dogs to run using command.com. No one tells you how to do it because MS really does not want the support headaches. Having said that in system32 is a true copy of command.com which when you make a shortcut to it with give you all of the old memory management types in the dialog box that were available under Win9x.

Some of these programs check to see if EMS UMB's etc are available before they will start while Win-XP normally makes them available on first use. The shortcut to command.com allows you to change XP's behavior to allocate on program execution. That could get it working long enough to take some pressure off while you come up with a windows version.

cairtaker

Sounds like a really big job for access? I'm only avg with access and sql but do you think access will handle it? I've noticed that access tends to bog down pretty quick. Good luck, April 7, my security test.

Megadeth4168

The server that the old system is running on is an IBM As/400 system. The Software uses a TN5250 terminal emulation system to connect. The software being Client Access for Windows 95/NT. We can't upgrade or anything because we discontinued support a long while back.

In about a year from now there is supposed to be a new system that many cities will be switching to (including us) however until that time we need to have some kind of temp solution.

We started having some issues installing it on newer PCs running Win2k but all the older Win2k machines seem to run the software just fine. The problem I am running into with XP and even some of the newer Win2k installs are connection issues.

I have the hosts file modified the same as I do the older PC and I have the firewalls turned off, however I can't seem to connect with XP. I suppose I could continue to search for the issue and fix it but in the meantime I was given the task to create a DB incase the old system fails anyway... As I said we don't have support on the old system and don't plan on picking up support for it.

Megadeth4168

cairtaker wrote:

Sounds like a really big job for access? I'm only avg with access and sql but do you think access will handle it? I've noticed that access tends to bog down pretty quick. Good luck, April 7, my security test.

I do a lot of access projects here, so I am used to the big access projects. I do have to agree with you that access becomes bogged down quickly. Also It's not practical for really big DBs, iirc an access db can only get up to around 2Gb in size... Maybe that's changed since my classes on access.

Honestly I don't think access should be used long term for this system, it should hold out until the new system is inplace some time next year though.

BTW, good luck on you exam. I havent signed up for mine yet.... I've been slacking a little bit on the study

cairtaker

Trying to say focused, but some days its hard to pick up the books, but I'm pushing this thing hard. I want to have security+ and then server+ done by June 1st. If I work hard enough I should be able to. I just have to keep chugging along.

2lazybutsmart

Notes...?? That's like giving access something to choke on. I think the NoteID method you mentioned in your first post is the best solution. Coupled with a simple VB program that will display the notes on a rich text box control or something like that, you're done.

2lbs.

Megadeth4168

2lazybutsmart wrote:

Notes...?? That's like giving access something to choke on. I think the NoteID method you mentioned in your first post is the best solution. Coupled with a simple VB program that will display the notes on a rich text box control or something like that, you're done.

2lbs.

I agree, I'm not at the point in the DB to work on that part yet... Close, when I go back to work on Monday I will probably be clsoe to the point to try and work on that.
I just have to figure out how exactly to do that.

Megadeth4168

OK! I must just be stupid or something but here is a question for all of you.

I have a table with a field that uses date/time... This field needs to include the time that a call is made.... So... I am using the Now() expression as a default value on the date field.

I thought would be fine except that I am finding that when I go to the next record the date is only accurate if the user enters information right away....

In other words if they get a call and and fill out the form then click to the next record on the form the date will be auto filled in which would be fine if they have another call to document but it is not fine if they leave the window sitting there for several minutes.

I was simply trying to make a button so that On Click it would update the time in the field on that table! Unfortunately I keep hitting errors trying to do this even though it should be easy right?

I'm wondering now if I can even do this... Any ideas?

2lazybutsmart

I would include the timestamp (the Now() value) in the INSERT query being executed against the recordset. This ensures consistency and reduces errors (which I still don't understand how they're popping up in your scenario).

Again, there are many ways of doing the same thing, so you'll just have to do it the way that works best with your applications.

2lbs.

Megadeth4168

I must just have something wrong with the way I'm setting it up in the button.

The following is from the expression builder... I set up the button to event On Click to perform the code.

=[tbl-rms]![Reported Date/Time]=Now()

I've also tried a macro using the SetValue option where I used the above table and record for item and the now() for expression.

I know there are other ways to go about this but it does frusterate me that I couldnt get this working... I know it is probably something stupid that I'm doing or not doing.

Silver Bullet

Try renaming the table to Reported Date Time without the "/". Then try the Setvalue with the new name and see if it will work. Special characters should be avoided.

Megadeth4168

I've tried altering other fields as well as a test that don't use special characters and have tried from different tables... Getting the same error everytime.

Silver Bullet

Megadeth4168 wrote:

I've tried altering other fields

I apologize. I was referring to the Table name..... but now I see that the table name has a "-". Rename the Table to read tblrms and the field to read Reported Date Time. (Be sure that you rename the field in the Table and the Form)

In other words just remove the special characters form your "fields" and "tables". If your forms have special characters then rename them as well.

I am assuming that since you have created a command button then you have a form created from the table [tbl-rms]. You will want to have the Macro set the Date in that form.....not the table

In the SetValue...it should look like this after renaming.

Item: [Forms]!i]your form name[/i![Report Date Time]
Expression: Now()

Or

Item: [Report Date Time]
Expression: Now()

Note that there are NO = before the Item or the Expression.

Megadeth4168

Silver Bullet wrote:

Megadeth4168 wrote:

I've tried altering other fields

I apologize. I was referring to the Table name..... but now I see that the table name has a "-". Rename the Table to read tblrms and the field to read Reported Date Time. (Be sure that you rename the field in the Table and the Form)

In other words just remove the special characters form your "fields" and "tables". If your forms have special characters then rename them as well.

I am assuming that since you have created a command button then you have a form created from the table [tbl-rms]. You will want to have the Macro set the Date in that form.....not the table

In the SetValue...it should look like this after renaming.

Item: [Forms]!i]your form name[/i![Report Date Time]
Expression: Now()

Or

Item: [Report Date Time]
Expression: Now()

Note that there are NO = before the Item or the Expression.

I got rid of all the - and / nonsense in this dtabase and did as you said in the macro and it works! Thank you so much!

I guess it's back to my original question now...

I know how to make a button that will open word, but how do I make a button that will create a new word document with the same case number as the record I am on? Also how would I associate that word document with that record? I assume this will require a good deal of Visual Basic knowledge.

Silver Bullet

Megadeth4168 wrote:

I guess it's back to my original question now...

See if this will help you accomplish what you are wanting to do.

Megadeth4168

Not sure if that is exactly what I am looking for...

Would an OLE object work? I've played around with it a bit and see that each record has it's own notes object which is what I want, the only problem is that I don't know where access is storing this information...

What will end up being a problem later on is if/when we have to convert the DB to a new system.... I doubt the OLE field will convert and I know that if access is not making a new document for this system then it wont get done. I know the people using it won't make copies.

Here is another issue that was brought to my attention... If/When we do convert to a new system it will be easier to convert something in notepad rather than Word.

So what are my options right now? I need to let the users have the ability to enter as much text as they need for case notes which can sometimes be 40+ pages. I don't think access (even memo field) can handle this.

I think I need an outside source of data but I can't rely on the users to make that. This needs to be as user friendly as possible... Would be nice if they could just type their notes right into the access DB on a form field and have that field automatically save a txt or word documnet in an external file.

This is a frusterating Project.

1. needs to be user friendly
2. access needs to create an external text file or word file
3. Each file should relate to the case number it was written for
4. Each file hopefully would be named using the case number field

Can this even be done? If so, I would probably need the actual case file text documents to be stores in a seperate directory.

I'm OK with access but I know this beyond me

2lazybutsmart

You can accomplish all of that in a simple --very very very simple -- VB application. Heck, you can even do it with a few lines of asp code.

Really. This isn't frusturating at all if you don't make it so. It's very simple. Are you familiar with VB or asp coding?


2lbs.

cairtaker

Come on 2lazy, I know you've got an idea already put together, don't keep us hanging, give it up. Pleeeeeeease!

Megadeth4168

2lazybutsmart wrote:

You can accomplish all of that in a simple --very very very simple -- VB application. Heck, you can even do it with a few lines of asp code.

Really. This isn't frusturating at all if you don't make it so. It's very simple. Are you familiar with VB or asp coding?


2lbs.

No, I'm not really strong with coding... I play around with C sometimes for the game my friends and I run online but we purchased the source code... I'm not really strong with it and can only add small things to the code.
So, basically if you had some code for this I could more than likely understand it, add to it ect... But I don't think I could write my own code.

2lazybutsmart

Well the fact is that you should try to get a good grasp of coding if you intend to be more flexible with making applications. I'm not saying Access isn't good, I'm just saying it's better to use the proper tools and since Access is more DBMS than application programming, you'll find more difficulty with it when it comes to doing applications.

Ironically, I've seen people do fabolous programs with Access but then, of course, they were limited in terms of what they could accomplish.

2lbs.

Megadeth4168

This specific project is unlike anything that I would normally do. I don't expect to work on something like this again anytime after this project actually.

Most of my Access experience has been simple inventory DBs. Something that an entry level Access class in college could accomplish.

I honestly don't plan on doing a lot of coding at my job, this is just one of those very rare occassions in the past 6 years where I have need to actually do any programming.

Really I think this is the last element of my Database needed to finish up this project.

Megadeth4168

I suppose if I knew the correct coding I could just simply use some of the exsisting code that the wizrad creates when making a button that opens word.

All I would need to know from there would be how to make it create a new file with the name of the case number....

Set oApp = CreateObject("Word.Application")
oApp.Documents.Open ("c:\test.doc")

I can get that to work as far as opening a specific document... I know that's really no where close to what I want though

Silver Bullet

See if this is what you are looking for.

Megadeth4168

looks very promising... I will start working with that and see how far I get. Thanks!

Megadeth4168

OK... I am starting to get close to my deadline... I got it down to where I can program a button that will open word and save it as a certain file name and I got a button that can reopen a certain file name ect...

I'm missing one very important part though...
The Variables!

I need the button to be able to save teh document based on teh current Case Number (field inside the form) and I need the other button to be able to open that document....

I'm not really strong with VB and it has taken me a bit of effort to get to this point.
Many thanks to Silver Bullet for pointint me to some resources where I can look at code samples and get an idea of how things are structered.

Megadeth4168

The following is kind of an example of my code (my limited abilities combinded with several examples ect...)

I have this working, problem is that I noticed something really damaging when I tested it. If I hit the first button to create the document then make changes to the document and save all is well... I can use the second button to open the record again.... If I hit the first button by accident when there is already information in there then it overwrites what I had!

I hope this should be simple, all I need is some kind of code to check if the file under that name exsists... If it does it should give an error and exit. If all is clear it should allow you to proceed.

Below is the code I've been playing with (not cleaned up yet)
Any suggestions?

Private Sub Command3_Click()
On Error GoTo Err_Command3_Click

    Dim oApp As Object
    Dim strSaveName As String
        
        strSaveName = Nz(Me![Case Number])
        Set oApp = CreateObject("Word.Application")
            
            oApp.Visible = True
            oApp.Documents.Add

' Writes the word Case Number followed by variable on doc
    oApp.Selection.TypeText "Case Number: " & strSaveName
' Save the document.
    oApp.ActiveDocument.SaveAs filename:="P:\nav\" & strSaveName
 

' Clear the variable from memory.
   Set oApp = Nothing
Exit_Command3_Click:
    Exit Sub

Err_Command3_Click:
    MsgBox Err.Description
    Resume Exit_Command3_Click
    
End Sub


Private Sub Command5_Click()
On Error GoTo Err_Command5_Click

    Dim oApp As Object
    Dim strSaveName As String
        
        strSaveName = Nz(Me![Case Number])
    Set oApp = CreateObject("Word.Application")
    oApp.Visible = True

'Opens document in Word
oApp.Documents.Open filename:="P:\nav\" & strSaveName


Exit_Command5_Click:
    Exit Sub

Err_Command5_Click:
    MsgBox Err.Description
    Resume Exit_Command5_Click
    
End Sub

Megadeth4168

This Problem has been resolved.

I played with the code and did a lot of trial and error before I got it right but it is working perfectly now. I feel good taht I accomplished a good part of it by using my own abilities and the rest of it by looking at other examples to see how to get started.

Thank you for the help and resources!

Silver Bullet

Good Job!