Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    mejia.j88 is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Nov 2011
    Location
    california
    Posts
    228

    Question Help with simple vba

    hello all,

    i am extremely new to VBA, (in fact, started today). i need help in order to meet a deadline for a project i just picked up.

    our database has a form with a check box that marks the end of a lot for our product.

    If the part being built is the end, then the box is check, if it's not the end, then its not. after clicking save a label is printed, and it should print the following
    End: yes (or End: No)

    the yes an no comes from the check box, yes as -1 and 0 should be used for no.

    i need to convert the -1 and 0 to "yes" and "no" respectively. I toyed with the following code:

    private sub form_load ()
    dim End_of_lot as boolean
    dim End_Text as string

    [End] = End_of_lot
    if
    [End] = true
    then
    CStr(End_of_lot) = "yes"
    else


    Cstr(End_of_lot) = "no"
    end sub


    but dont have the knowledge to actually make it work.

    any help please???

  2. #2
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Instead of having code in the On Load event of your Form, I think you should put the code in the On Click of your 'Save' button.

    The code should look something like this:

    Code:
     
    Private Sub cmdSave_Click()
    Dim strYesNo as String
     
    If Chk1.Value = True Then
        strYesNo = "Yes"    
    Else
        strYesNo = "No"
    End If
     
    End Sub
    Hope this helps.

  3. #3
    mejia.j88 is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Nov 2011
    Location
    california
    Posts
    228
    Robeen, this has helped tremendously, yet it's still printing as -1 or 0.

    my code now looks like this:

    Private Sub txt_save_click()
    RunCommand acCmdSaveRecord

    'prints end of lot "Yes" or "No" on label
    Dim strYesNo As String

    If Check24.Value = True Then
    strYesNo = "Yes"
    Else
    strYesNo = "No"
    End If

    Call PrintLabels
    DoCmd.Close acForm, Me.Name
    End Sub


    thanks!
    jm

  4. #4
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    What is in 'PrintLabels'?

    It looks like your PrintLabels routine is getting the checked property of the checkbox from the Form and assigning the 'Value' [0, or -1].

    I'm guessing that you'll have to make a change to the code in PrintLabels to put a "Yes" in if it IS checked and a "No" if it is NOT checked.

  5. #5
    mejia.j88 is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Nov 2011
    Location
    california
    Posts
    228
    Print Labels asks the user how many labels they want printed:


    Private Function PrintLabels()
    Dim Show_Box As Boolean
    Dim Response As Variant
    Dim i As Integer

    ' Set the Show_Dialog variable to True.
    Show_Box = True

    ' Begin While loop.
    While Show_Box = True

    ' Show the input box.
    Response = InputBox("Enter the number of labels to print or press Cancel to skip printing.", "Label Printing", 1)

    ' See if Cancel was pressed.
    If Response = "" Then

    ' If Cancel was pressed,
    ' break out of the loop.
    Show_Box = False
    Else
    ' Test Entry to find out if it is numeric.
    If IsNumeric(Response) = True Then
    For i = 1 To Response
    DoCmd.OpenReport "rpt_Inspection_Label", acViewNormal, , "[ID]=" & Me.ID
    Next i
    Show_Box = False
    Else
    ' If the entry was wrong, show an error message.
    MsgBox "Please Enter Numbers Only"
    End If
    End If
    ' End the While loop.
    Wend
    End Function



    would i need to make a connection between strYesNO and Check24.value?

  6. #6
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    I think PrintLabels is doing more than just asking how many labels are required.

    What does this line in the code do?
    Code:
     
    DoCmd.OpenReport "rpt_Inspection_Label", acViewNormal, , "[ID]=" & Me.ID
    It is being executed the same number of times as the number entered by the user - right?

    Your "Yes" and "No" [0, -1] are being picked up by the report "rpt_Inspection_Label".

    I'm guessing that the 'End-Of-Lot' is a Boolean [True/False] field in a Table on which the report is based.
    Is that correct?

  7. #7
    mejia.j88 is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Nov 2011
    Location
    california
    Posts
    228
    that line of code opens a report in which i have formatted the label being printed, the information comes from the form via query. [ID] is to print the record that was just saved, as opposed to every record in the table.
    it makes sense to me that this is being executed the number of times entered by the user.

    end of lot is a checkbox on the form, so when it is saved onto the table it is saved as -1 for yes and 0 for no.--yes.

    [0,-1] are being picked up by rpt_Inspection_label, but i would like it to appear there as yes and no.

    should the code you suggested be under the report instead of under cmdSave_click() ?

  8. #8
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    I think you can make the "Yes" and "No" - happen in your query.
    You're saying the report is based on a query - right?

    Can you post the SQL of your query here for me to look at?

  9. #9
    mejia.j88 is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Nov 2011
    Location
    california
    Posts
    228
    SELECT tbl_Die_Inspection.ID, tbl_Die_Inspection.Dicing_lot, tbl_Die_Inspection.Part_Number, tbl_Die_Inspection.Qty_In, tbl_Die_Inspection.Qty_Out, tbl_Die_Inspection.Operator_No, tbl_Die_Inspection.Date_Time_Inspected, tbl_Die_Inspection.Work_Order_Inspection, tbl_Die_Inspection.Diffusion_Lot, tbl_Die_Inspection.Evaporation_lot
    FROM tbl_Die_Inspection
    ORDER BY tbl_Die_Inspection.ID DESC;

  10. #10
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    I was expecting to see 'end-of-lot' in the query as one of the fields.

  11. #11
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    I mean . . . your label is based on your report - right?
    And your report is based on the query - right?

    So - which field in your query is giving you the '0' and the '-1' ?

  12. #12
    mejia.j88 is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Nov 2011
    Location
    california
    Posts
    228
    sorry, i just added it on there, the field in the query that is giving 0 and -1 is [End]


    SELECT tbl_Die_Inspection.ID, tbl_Die_Inspection.Dicing_lot, tbl_Die_Inspection.Part_Number, tbl_Die_Inspection.Qty_In, tbl_Die_Inspection.Qty_Out, tbl_Die_Inspection.Operator_No, tbl_Die_Inspection.Date_Time_Inspected, tbl_Die_Inspection.Work_Order_Inspection, tbl_Die_Inspection.Diffusion_Lot, tbl_Die_Inspection.Evaporation_lot, tbl_Die_Inspection.End
    FROM tbl_Die_Inspection
    ORDER BY tbl_Die_Inspection.ID DESC;


    it's at the bottom.

    thanks so much for helping me

  13. #13
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Try this:
    Code:
     
    SELECT tbl_Die_Inspection.ID, tbl_Die_Inspection.Dicing_lot, tbl_Die_Inspection.Part_Number, tbl_Die_Inspection.Qty_In, tbl_Die_Inspection.Qty_Out, tbl_Die_Inspection.Operator_No, tbl_Die_Inspection.Date_Time_Inspected, tbl_Die_Inspection.Work_Order_Inspection, tbl_Die_Inspection.Diffusion_Lot, tbl_Die_Inspection.Evaporation_lot, 
    IIf([tbl_Die_Inspection].[End] = 0, "Yes", "No") As EndOfLot
    FROM tbl_Die_Inspection
    ORDER BY tbl_Die_Inspection.ID DESC;
    What that IIf Statement is saying is 'If the Value in End is 0 - replace it with a "Yes" - otherwise replace it with a "No".

    Let me know if that works for you.

  14. #14
    mejia.j88 is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Nov 2011
    Location
    california
    Posts
    228
    There might be a problem with the SQL, when i hit the save button, the

    Enter Parameter Value box comes up, asking End.

    :S

  15. #15
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Have you tried running your query on it's own - not from the Save button - using the IIF function?
    You'll have to get it running as a query first before you can use it in your program.

    Change this:
    IIf([tbl_Die_Inspection].[End] = 0, "Yes", "No") As EndOfLot

    To:
    IIf(tbl_Die_Inspection.End = 0, "Yes", "No") As EndOfLot

    Or:
    IIf([End] = 0, "Yes", "No") As EndOfLot

    Or:
    IIf(End = 0, "Yes", "No") As EndOfLot

    See if one of those works.

    I'm uneasy about using 'End' as a field name.
    'End' is a reserved word in VBA and SQL - & I try and stay away from those - just to avoid possible complications.

    But the problem may be in the SQL I gave you.

    Let me know how it goes.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Simple QBE
    By MikeDBMan in forum Queries
    Replies: 6
    Last Post: 08-31-2011, 02:37 PM
  2. Need some help with simple VBA Thanks
    By everette in forum Programming
    Replies: 1
    Last Post: 08-07-2011, 08:32 AM
  3. Hopefully simple If Then
    By fender357 in forum Programming
    Replies: 4
    Last Post: 05-09-2011, 07:52 PM
  4. Replies: 0
    Last Post: 10-21-2010, 08:24 AM
  5. Simple Export Not So Simple
    By jgelpi16 in forum Programming
    Replies: 11
    Last Post: 09-01-2010, 07:23 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums