Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Oct 2006
    Posts
    39

    Unanswered: Need help with 2 small vba codings related with msgboxes and lostfocus

    A modified Employee form where the necessary VBA code is written so that on the lostfocus event of the email address, this last is checked whether it follows the pattern “myusername@myhome.com”. An error message shall be shown when appropriate.

    the email field is called "emailmc"



    A modified delivery form where the appropriate VBA code is written so that on the lostfocus event of the delivered quantity, the total price of the delivered items is computed and placed on a message box.

    delqtymc is the delivered quantity.
    Im not sure about this question though, because there is not price in that form.

    but if anyone can know how to do the 1st paragraph question. It's fine.
    thanks in advance.

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Problem # 1

    The red code checks to see if the data in emailmc ends with ".com"

    The blue code checks that the @ is present in the data

    The green code checks that there are no spaces in the data

    Code:
    Private Sub emailmc_LostFocus()
    If (Right(emailmc, 4) <> ".com") Or (InStr(emailmc, "@") = 0) Or (InStr(emailmc, " ") > 0) Then
         MsgBox "Invalid Email Address"
    End If
    End Sub
    Be sure to copy the part of the green code that you have to scroll to see!

    Problem # 2

    Assuming the Unit Cost is unitcostmc and the total cost is totalcostmc:

    Code:
    Private Sub delqtymc_LostFocus()
         Me.totalcostmc.Value = Me.unitcostmc.Value * Me.delqtymc.Value
    End Sub
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Oct 2006
    Posts
    39
    [QUOTE=Missinglinq]Problem # 1

    The red code checks to see if the data in emailmc ends with ".com"

    The blue code checks that the @ is present in the data

    The green code checks that there are no spaces in the data

    Code:
    Private Sub emailmc_LostFocus()
    If (Right(emailmc, 4) <> ".com") Or (InStr(emailmc, "@") = 0) Or (InStr(emailmc, " ") > 0) Then
         MsgBox "Invalid Email Address"
    End If
    End Sub
    Be sure to copy the part of the green code that you have to scroll to see!

    The right,instr, and instr all need to be dimed right?

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    No, they're standard VBA functions.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    To learn exactly how they work (a good idea if you're going to use them!) in the code window, place the cursor in the middle of each of the functions and press <F1>.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Oct 2006
    Posts
    39
    Missinglinq, thanks a lot. You're awesome.
    i got the 1st one to work. i will try out the 2nd soon.

  7. #7
    Join Date
    Oct 2006
    Posts
    39
    Quote Originally Posted by Missinglinq
    To learn exactly how they work (a good idea if you're going to use them!) in the code window, place the cursor in the middle of each of the functions and press <F1>.

    thanks a lot Missingling.
    the 1st one works. i will try the 2nd one soon.

  8. #8
    Join Date
    Oct 2006
    Posts
    39
    For number 2.

    It wants to display in a msgbox, not in a totalboxmc value.

    the unitprice is from another table "itemMC"
    and the item price is called = "itempriceMC"
    with
    delqtyMC

  9. #9
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    WE can deal wwith the messagebox thing, I think, but first: how do you have the second table related with the first. What is the Primary Key in one that is the Foreign Key in the other.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  10. #10
    Join Date
    Oct 2006
    Posts
    39
    Quote Originally Posted by Missinglinq
    WE can deal wwith the messagebox thing, I think, but first: how do you have the second table related with the first. What is the Primary Key in one that is the Foreign Key in the other.

    Heres the pictures of the 2 tables in the design view.
    http://i8.photobucket.com/albums/a35...delitemkey.jpg

    So we want to implement this msgbox in the delivery Form that i created with the deliery table data.

    so the primary key is itemname for the item table. so the forgien key is itemname in the delivery table.

  11. #11
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    And the Record Source for your delivery form is a query based on these two tables?
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  12. #12
    Join Date
    Oct 2006
    Posts
    39
    Quote Originally Posted by Missinglinq
    And the Record Source for your delivery form is a query based on these two tables?
    the delivery form is just based on the deliery table.
    there was no query made for this.

  13. #13
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    In order to do your calculation, you're going to have to get your itempriceMC from the ItemMC Table. You're only allowed one Record Source for a form, so you need to make a simple Select Query using both tables, joining them by the itemnameMC fields. You then need to go to the Form's Property Box and under the Data tab, change the Record Source to the name of the query. Make sure you have a control on your form for itemnameMC and itempriceMC.

    The code below will place the total cost of the order in a message box. You'll need to play with it to get it to display in dollars, or whatever currency your machine is set for. I'm in the middle of a major virus attack, and will be busy the rest of the day, I suspect. If you have any questions post them back here; someone else will probably catch it and help. I'll look back when I can. Good luck!


    Code:
    Private Sub delqtymc_LostFocus()
         Dim TotalCost As Currency
         TotalCost = Me.itempriceMC.Value * Me.delqtymc.Value
         MsgBox "Total Cost of Items: " & TotalCost
    End Sub
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  14. #14
    Join Date
    Oct 2006
    Posts
    39
    Quote Originally Posted by Missinglinq
    In order to do your calculation, you're going to have to get your itempriceMC from the ItemMC Table. You're only allowed one Record Source for a form, so you need to make a simple Select Query using both tables, joining them by the itemnameMC fields. You then need to go to the Form's Property Box and under the Data tab, change the Record Source to the name of the query. Make sure you have a control on your form for itemnameMC and itempriceMC.
    Will that make me have to make another form also?
    or you telling me to use that query inside the delivery form.

    i believe i should only use one the regular delivery form that i already made.
    not a 2nd delivery form.

    Or a better idea is to make that and replace the orignal delivery form.
    Last edited by Chanyeehon; 11-19-06 at 23:11.

  15. #15
    Join Date
    Oct 2006
    Posts
    39
    ok.
    I think i followed your procedures correctly. i didnt follow urs directly since my way will prolly make the same results.

    I made the query, with the itemmc and deliverymc table.
    and made the query into a form called delivery form.

    i put the code in.

    NM
    it works!!!
    seems so wierd.
    i added...
    Dim TotalCost As Currency
    TotalCost = Me.itempriceMC.Value * Me.delqtyMC.Value
    MsgBox "Total Cost of Items: " & FormatCurrency(TotalCost)


    i dont get what does Me.itempriceMC.Value * Me.delqtymc.Value

    what does Me. mean?
    Last edited by Chanyeehon; 11-19-06 at 23:41.

Posting Permissions

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