Help with Access file

[leerph]

So let me start by saying I know almost nothing about access and I'm hoping my question can be answered easily by all the experts on here. We own a computer repair store and several years back we had a guy build us an access database that would allow us to track incoming items dropped off for repair as well as mark them complete, etc. It has served us well for many years but I need to change just a couple small things and the guy we paid is no longer around. I am hoping you guys can help me out. I will attach a copy of the database for you to look at. Basically all we want to do is add a new entry and make it a "required" entry with the name "$45 Bench Fee Paid" and then have a drop down box that has either yes or no. The only other change we need is that there is currently a drop down box in there for "Technician" where we can select what technician took it in, but that particular selection doesn't actually print out on our work order when we hit print. It only shows up in the program. We would like for the "Technician" field to also become a required field and then have that field print out onto the work order when we choose print. If there isn't room on the form then we can remove one of the other items that does currently print on the form such as "HD Password" to make room for that one to print. if this is something any of you guys can figure out for me or walk me through I sure would appreciate it. I tried to look into it but it's just beyond my scope as I have had no training in Access. Thanks in advance for any and all advise.

[Micron]

add a new entry and make it a "required" entry with the name "$45 Bench Fee Paid"

Where?

there is currently a drop down box in there for "Technician"

Where?
Your technician form and work order form won't open. Might be an easy fix, then again maybe you already know that and aren't concerned about it. Or this is a new db that you imported everything into but forgot that table? Also, what seems odd is that both of these forms are dependent on the same table (which as I say, appears to be missing). I checked that there are no hidden fields in tblWorkOrder because it's name made me think that was supposed to be the one, but there are fields on these forms that are not present in that table.

[leerph]

Hi. This is my original copy of the database without all my customer info. It only has 3 sample work orders in it that I was using for testing. I'm not sure about your other questions? Is there maybe a problem with my zip file? There should be 2 files in the zip. One called fastlane.accdb and the other called fastlanedat.accdb When i double click the first one it opens up in my access version 2007 and works fine. It starts at an opening screen with the options open work order, open report switchboard, and exit. Once I go into "open work order" this is where we input all our data to add a new customer and input in what device they are dropping off, what accessories they left with it, what the problem is etc. A few of the boxes are required fields and marked with an asterisks. Once we finish inputting the data we hit the button at the bottom that says "print, save and close current work order and that makes the work order print out. If you input some test data and hit that button you will see the form that prints out. As stated, not all the info on the input area of the database actually prints out onto the form. In particular, there is a technician drop box in there that doesn't print out on the work order. On this new database that drop down box is empty. The way the guy set it up is that when we want to add a technician we go in and add them like they are a customer and in the box for company we list our company "Fastlane Computers". This prompts the database to know that this is an employee and then their name gets added to that dropdown box for technician. As stated, ideally we would like for that technician box to start printing on the work order. We really don't care where in the layout it prints out at and if there's not enough room we are willing to lose one of the things that currently does print out on the form such as "HD password" to make room. Same thing goes with adding a new box in there for "$45 Bench Fee Paid" and having it being a required drop down box with yes and no and then having that info also print out onto the printed copy of the form. Hopefully that makes sense. Like I said, I really know nothing about access. This database we paid good money for could be a huge laughing stock to all you gurus out there. LOL.

[leerph]

Just to double check I just downloaded my zip file and then extracted it using winrar and the 2 files are in there and they work as expected with no errors on my end.

[Micron]

Well, it's probably forms that you're not using. Understand that when someone posts a db and asks for help there are things that I (at least me anyway) will do to make it easier to see what's what, navigate and to some degree, protect myself from harmful code. That means I'll see things that maybe you as a non developer aren't aware of which can cause a bit of confusion at first. I think I know what form(s) you are referring to now so I'll take a look tomorrow. It is nearly 1 AM here now.

FYI - it does need some work to raise it's level of 'perfection', some of which is not too onerous. One very 'amateurish' oversight is not requiring variable declaration. When I employed that and tried to compile the project it failed. Maybe you've never experienced any ill effects of that, or maybe you did get odd errors from time to time that didn't seem to amount to much - or maybe you will eventually.

[leerph]

That would be awesome. Thanks for any help you can provide.

[mike60smart]

Hi

I imported both files into a New Db and added the two fields to the Work Order table.

I added anew table called tbluTechnicians and added a Combobox to select a Technician. If the Technician is not in the List the you can just type the New technician name into the ComboBox and hit Enter. Follow the promts to accept the New Name.

As Micron said there are some VB issues that I do not have enough experience to resolve.

[orange]

I looked at Mike's database and reformatted the relationships for readability. Hope it's helpful.

[leerph]

Thanks so much for all your help. When I download the new file and open it I get a Microsoft Visual Basic that pops up and says "compile error: variable not defined". I have to hit OK there and then close visual basic which gives an error that says "this command will stop the debugger". After I hit OK there visual basic closes and the database pops up in access but there is another error there that says "action failed" and I have to hit "stop all macros". At that point I can see the database and the changes that were made. What am I doing wrong. I assume it's something simple that will give you guys a good laugh. LOL. The new layout looks like exactly what I need. I haven't been able to play with whether or not the 2 new items added for "Technician" and $45 Bench Fee Paid are now required or not, but for the $45 fee is it possible to make it a drop down box with Yes or No b/c sometimes we don't charge it?

[Micron]

Hmmm...maybe mike60smart added Option Explicit, or you finally encountered what I was referring to. I'm out now so cannot look until later.

[davegri]

Mike60 added Option Explicit to modules but failed to correct compile errors. That's not so hard to fix by adding a few DIMs.
Also Mike converted the BE tables to local tables in the FE. Not so hard to fix by deleting the local tables and adding them back as links from the BE.
But that caused a problem. The new fields in tblWorkOrder were put in the local table. After relinking the BE, those fields were missing. I added them to the BE table.
Mike's new table, tbluTechnician is a local table.
BUT there's a problem. In the original OPs download, there's a table named MSysSettingsFr, which turned out to be a local hidden table. I had to copy that table to Mike's FE (which was not an FE until I deleted the tables and relinked them as noted before).
To copy the table, I had to right click it in the Navigation Pane in the original FE and export it.

So here's a working copy (I hope) with Mike's Technician and $45 bench additions. It has option explicit in all modules and no syntax errors, no runtime errors.

But the OP still has a problem. How to get his existing data into the revised tblWorkOrder in the BE, since the BE he posted has dummy data.
Best solution for OP, use posted FE since it has changes to forms, code and reports. Then add these two fields to tblWorkOrder in the PRODUCTION BE: Then toss the test BE.
While working with the BE, he can move tbluTechnician there from the FE if so desired.



Fastlane-davegri-V2.zip

[ssanfu]

Just a question to think about...

<snip> add a new entry and make it a "required" entry with the name "$45 Bench Fee Paid" and then have a drop down box that has either yes or no. <snip>

Will the bench fee ALWAYS be $45?
I would suggest that you have a table (tblBenchFees) with values of $0, $45 . Set the default value to $0. This way, if you want to change the bench fee next year to $55, you can add it to the table and not have to change the table or programming.
On a report, you could bind the "BenchFeePaid" field to a check box control - if you didn't charge a fee ($0), the check box will be unchecked and if a fee was paid (doesn't matter the value as long as it is not 0), then the check box will be checked (True).


I also noticed lots of VBA issues..... "Option Explicit" was already mentioned and the undeclared variables.
1 more thing is HOW some variables were declared.

    Dim val, taxRate, tax, tot, prreq, subtot As Double

Declares variables val, taxRate, tax, tot, prreq as Variant and only subtot as a Double.
Variable types MUST be explicitly declared.

[leerph]

Thanks for all the replies. The latest one is definitely on the right track as far as what I'm looking for but the 2 new fields that were added "$45 bench fee" and "technician" are still not required to be entered. In other words we can type up the form and leave those 2 fields blank and it will still let you save and print it versus with the other fields that are "required" if you leave any of those blank and try to save or print it, it will pop up with an error telling you that there are required fields that are empty. Is it possible to make those 2 new fields work that same way? As far as the suggestion for bench fee, I'm open to doing it different ways. It can be a drop down box with yes or no and you have to pick one, or it can be were it has different dollar values as suggested and you have to pick a value and are able to add new values. My main issue is that currently we get units checked in and later on the person that checked the item in can't remember if we collected our bench fee or not. In addition, all the techs get amnesia in regards to who checked them in. LOL. That's why I want to make those 2 fields required and then I can look and see which techs are screwing up, etc. As far as the vba issues, I honestly don't even know what that means. Is that something easy to fix? If it's a lot of work I certainly don't mind paying an expert here to help me get it updated the way it needs to be. It has been a good db that has served us well over the years for the limited needs that we have and with those 2 updates I think it can continue to serve us well.

[davegri]

Fastlane-davegri-v3.zip

See attached above. Changed form to add asterisk beside new required fields. Found existing code that flags existing required fields and added the new fields into that code, so it works the same for all.
There's no way to check a boolean field for whether it's updated or not. If it's true, was it updated? If it's false was it updated? No way to tell. So I changed that field in the BE table to short text and changed the form control from checkbox to combobox with dropdown. Now it can either be blank or contain "YES" or "NO". If it's blank it's not updated.
There will be one change required to the production BE as below:



Edit: Also, the checkbox for Bench fee on the work order report needs to change to a textbox so that it can show "Yes" or "No". This is not changed in the attached FE.

[Micron]

To make any field required, the easiest is to open the table in design view (cannot do this in front end), select the field you want to make required and change "required" to yes. I would not set 'allow zero length' to yes as some of yours are. You probably should leave that alone though. As for the code, not hard to fix. Some of the comments made about that were on my radar, but so were other things. While I agree with the idea of a bench fee selection, I would not make this $0 by default. IMHO, that would be as bad as what you have now because you would not be able to force users to make a choice as long as you're letting Access do it for you. You could end up with lots of zeros, same as now where you have lots of nothings.