Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Oct 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VB Code (Access 2000)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VB Code (Access 2000)

    VBA code is not the first thing I would think of here. A lot can be accomplished using queries. Take a look at the attached demo database. The query qryExclude returns equipment that is NOT available for each job, because it has already been assigned to another job that overlaps in time. The query qryAvailable returns equipment available for each job (including equipment already assigned to that job).

    Note: you don't need an AutoNumber field in tblEquipDetail; you can (and should) make the combination of JobID and SerialNum the primary key of this table. Neither JobID nor SerialNum will be unique, but the combination should be unique.

  3. #3
    Star Lounger
    Join Date
    Oct 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Code (Access 2000)

    Thank you so much! I can't wait to try out your suggestions when I get home. Thanks again!!!

  4. #4
    Star Lounger
    Join Date
    Oct 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Code (Access 2000)

    Hans,
    I have a three year old so I had my hands full last night. I'm trying to work on this during my lunch break.

    Thank you for your sample database, if you had typed an explanation out on all of that, I would have been completely lost! I'm more comfortable with queries so I can hardly wait to try it out.

    I have a question though. Where did the T table come from in qryAvailable?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VB Code (Access 2000)

    T is an alias for tblJob. If you select View | SQL, you'll see

    SELECT t.JobID, tblEquip.SerialNum, tblEquip.Type, tblEquip.Description
    FROM tblJob AS t, tblEquip
    WHERE ...

    Select View | Design View to restore the original view. The alias is necessary to avoid confusion in the criteria for SerialNum.

  6. #6
    Star Lounger
    Join Date
    Oct 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Code (Access 2000)

    I found it. Thanks!

    There's sure a LOT to learn!!!

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VB Code (Access 2000)

    There certainly is, but the reward is great - once you get the hang of it, you can accomplish things that would be very difficult or impossible in other applications. Access is very powerful, and consequently there is a lot to learn.

  8. #8
    Star Lounger
    Join Date
    Oct 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Code (Access 2000)

    I have a L O N G way to go but I'm enjoying the journey! Thank you so much for the support along the way.

  9. #9
    Star Lounger
    Join Date
    Oct 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Code (Access 2000)

    Hi Hans,
    Would you mind helping me again? I know that it's been awhile but with a full time job and a three year old there isn't a lot of free time. It's Father's day and my husband is napping with my son so I thought that it might be a good time to take a stab at this again. The queries that you created for me worked great! It seems like with every new thing that I learn I have a hundred new questions though.

    I put the Job information on a form (frmJob) and added a subform based off of your qryAvailable (subfrmAvailable). I can easily see, for each date range selected, what equipment I have available. Concerns/Questions/Plea for Advise

    1. Once tblEquip is filled in with live data and they go to frmJob to view the Available Equipment, they are going to have to do a lot of scrolling because they have a lot of different Types of equipment. I can use the filter button on the tool bar to filter on the type of equipment that I want but when I unfilter it to select the next piece of equipment, it unfilters everything including my job. I'm left on a different record all together! I thought about a parameter query but they would be operating so blind using that, you know they would have to remember all of the equipment types and spell it correctly in order to return accurate data. I would probably end up in the same boat anyway, so I didn't go there. With my . . . limited knowledge <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15> 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!!! <img src=/S/clever.gif border=0 alt=clever width=15 height=15>

    2. My second concern is, when I put your queries in place and then checked it against the test data that I had entered previously, I had already overbooked some jobs. Can you please tell me how to make a message appear that says, this equipment is already booked, are you sure you want to select it? Yes= accept it or No = cancel or delete selection.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VB Code (Access 2000)

    1. I think you should have a subform based on tblEquipDetail (or a query based on tblEquipDetail), not on qryAvailable. See modified version of previous demo.

    2. You should remove overbookings before taking the database into 'production'.

    See <post#=401925>post 401925</post#> for instructions on posting a stripped down copy of a database.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •