Okay, where do I start? I just finished my Access class and we touched on the VBA coding . I now know, just enough to know that I need to know more. I will take an additional class in the Fall but my husband is hoping to reap the rewards of the class now. Can someone please tell me how to write a code? I’m trying to build a small database for his company. They rent out equipment and are constantly overbooking jobs.
One Client can have many locations.
Each location can have many jobs.
Each job can have many pieces of equipment.
I’ve built the following forms:
frmClient – which they can use to add a location. subfrmLocation
subfrmLocation – which they can use to add a job. subfrmJob
subfrmJob – which they can use to add the equipment needed. subfrmEquip
tblEquip
SerialNum – primary key
Type
Description
tblJobID
JobID – auto number primary key
LocationID – foreign key
JStartDate
J#ofDays
tblEquipDetail
EqDetail – auto number primary key
JobID – foreign key
SerialNum – look up (SerialNum, Type, Description on tblEquip)
I’m working on, subfrmJob – which uses tblJobID as the record source and subfrmEquip, which uses tblEquipDetail as the record source. I’ve added a label that reads “This Equipment is NOT Available.” I have the property set as Visible – NO. (A trick that I recently learned in class which COULD work for what I want to do . . . we’ll see.) I’ve added an addition subform, subfrmAvail, with a query record source, qryCheckAvail. It contains all of the equipment currently booked, the Job Start Date, the # of Days the equipment that is booked and a calcualated field which gives the Job End Date.
What I hope to do, which may or may not be the right approach, is write a code that looks at the serialnum that is selected on subfrmEquip and check it against what is already booked from subfrmAvail. If there is a match then I would like it to check the dates that it is currently booked and if it is booked in the date range that was selected on subfrmJob then make the label appear that will let him know that the equipment is not available, or something along that line so that he doesn’t overbook his equipment. Does that make any sense. Gosh, I hope so because I’m frustrated to no end. I know that the instructor said that all of the information that you want in the coding should be on the form, even if it’s hidden, which subfrmAvail is a hidden form.
Any help or advise that you can offer will be appreciated. Perhaps, I’m going about it all wrong. I’ll be glad to go back to class in the Fall but other than this key code, I’m comfortable that I can build something better for my husband than the note pad that he’s using now. Can anyone help me get past this project? If you’ve built anything similar, I’m certainly open to suggestions.
because I know that Access can do more than I can even imagine, I thought that I could create a combo box based off of tblType, used when setting up new equipment. It only has one field in it and it’s a primary key so there aren’t any duplicates, tblType.Type. Anyway, I thought that I could create a combo box using tblType and then filter frmJob on whatever is selected in that box. I don’t have any idea whether that will work or not because all of the codes that I tried had errors in them. It sounds like an iif statement to me but I don’t know how to write it in the correct code. cboTypeafter update – If cboType is not null then filter subfrmAvailable on the Type selected else null or show all. I don’t know how to write it so the cboType and subfrmAvailable.Type are equal or if cboType is blank than show me everything available. I don’t know how to add an attachment to show you what I’ve tried but even then, nothing has worked. Boy, is my instructor going to have his hands full in the Fall – I have a thousand questions now!!! 
