Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Star Lounger
    Join Date
    Oct 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    If Statement (Access 2000)

    Please help me adjust a code. I'm trying to work with one of the samples from the class that I took last Spring but I'm botching it up. I really need it for a database that I'm building here at work. I have several questions so please bear with me.

    I have a check box [SPLIT] on a subform [sfrmDirectoryCodes], which when checked should bring up an Input box. The input box should ask them to enter in the member number. The member number would then go into the unbound text box [txtPurchasingShop] on the mainform [sfrmDirectory]. Once the member number has been filled in, I don't want the input box to pop up again for the same record on the mainform because they could be selecting several items at one time on the subform. It should pop up again if they go back in later and submit a new request though.

    1. Formatting the member number - The format has to be Two Numbers, a Hyphen, Four Numbers and then Two Letters. I don't know how to write that and wouldn't know where to put that into the code. # # - # # # #LL??? Can you PLEASE add this in to the correct code?

    2. Unbound text box - The class example used an unbound text box but I need mine to fill in a field on the mainform. txtPurchasingShop filling in field PurchasingShop#. PurchasingShop# is found in tblDirectory if that matters.

    This is what I tried:

    Private Sub SPLIT_Click()
    If [SPLIT] = True Then
    [txtPurchasingShop] = InputBox("Please enter the member number for the PURCHASING shop.", _
    "PurchasingShop#")
    If [txtPurchasingShop] = "" Then
    txtPurchasingShop.Visible = False
    Else
    txtPurchasingShop.Visible = True
    End If
    Else
    txtPurchasingShop.Visible = False
    End If
    If txtPurchasingShop.Visible Then
    End If
    End If
    End Sub


    3. I don't remember what this part of the code was for. I think that it referred to the text box label so I used the name of the field that I wanted to fill in instead.
    .", _
    "PurchasingShop#")

    The class example prompted other InputBoxes to pop up so I may have cut out too much or too little. It's probably not working because the example was all on one form and I'm trying to have the check box on the subform and the text box on the main form. Either way, I just don't get it.

    PLEASE HELP - I've tried adjusting it so many times my head is spinning.
    Thanks

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

    Re: If Statement (Access 2000)

    If you want to refer to a text box txtPurchasingShop on the main form from code running in a subform, you can use Me.Parent.txtPurchasingShop. "Parent" refers to the main form.

    #-####LL is not a format, it is an input mask, i.e. a property of a text box. An InputBox has not input mask. You can put
    <code>#-####LL;0;_</code>
    in the Input mask property of the txtPurchasingShop text box.

    If you click in the word InputBox in the code and press F1, you'll get online help. The first argument is the prompt text, the second the window title. In

    InputBox("Please enter the member number for the PURCHASING shop.", _
    "PurchasingShop#")

    or equivalently

    InputBox("Please enter the member number for the PURCHASING shop.", "PurchasingShop#")

    In this call, "Please enter the member number for the PURCHASING shop." is the prompt text and "PurchasingShop#" is the window title.

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

    Re: If Statement (Access 2000)

    Thank you Hans. I'll try it.

    How do I have the unbound text box fill in the correct field or can I just use the correct field for all of this instead?

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

    Re: If Statement (Access 2000)

    If you want the text entered by the user go into a field in the record source of the main form, you can use a text box bound to that field instead of an unbound text box.

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

    Re: If Statement (Access 2000)

    Perfect - thanks again.

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

    Re: If Statement (Access 2000)

    Hans,
    Will you please help me adjust the code so the input box doesn't keep popping up if the Purchasing Shop # has been filled in? Sometimes they are selecting 10 items and although I don't want the Rep to forget to enter in the purchasing shop code, I don't want it to drive them crazy either.

    Also, I couldn't get the Purchasing Shop to stay on the form if it was filled in. Every time I closed the form and went back into the same record, it was hidden again.

    Private Sub Split_Click()
    If [Split] = True Then
    Me.Parent.txtPurchasingShop = InputBox("Enter the member number for the Purchasing Shop.", _
    "Purchasing Shop")
    If Me.Parent.txtPurchasingShop = "" Then
    Me.Parent.txtPurchasingShop.Visible = False
    Else
    Me.Parent.txtPurchasingShop.Visible = True
    End If
    Else
    Me.Parent.txtPurchasingShop.Visible = False
    End If
    End Sub


    Thanks again,
    Theresa

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

    Re: If Statement (Access 2000)

    I would do away with the InputBox entirely. Instead, I would put code in the Before Update event of the form to warn the user if txtPurchasingShop has not been filled in.

    - In the On Click event of the check box:

    Private Sub Split_Click()
    Me.txtPurchasingShop.Visible = Me.Split
    End If

    - In the On Current event of the form:

    Private Sub Form_Current()
    Me.txtPurchasingShop.Visible = Me.Split
    End If

    - In the Before Update event of the form:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.Split = True And IsNull(Me.txtPurchasingShop) Then
    MsgBox "Please enter a purchasing shop (or clear the check box).", vbExclamation
    Me.PurchasingShop.SetFocus
    Cancel = True
    End If
    End Sub

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

    Re: If Statement (Access 2000)

    Hans! I didn't realize that you had replied back to me. I must have clicked it early this morning and I've only taken the time for new mail so it was overlooked.

    I have been pulling my hair out. They sent me to an access class in the Spring but gave me very little time to apply what I had learned. Now they expect me to rebuild a new database for the team overnight. My boss has been on vacation all week and is going to think that I've been sitting around doing nothing but honestly, I have been trying to remember the few codes that I learned so I could apply them. I frantically went through my class book and examples and have been able to apply some of the things that I learned but I'm about in tears.

    As I mentioned, I didn't realize that you replied back and I couldn't get anything to work so, I remembered what you had suggested at another time and I went with the tab control. It isn't really what I wanted to do but I'm afraid that, what I KNOW that it can do and what I have the knowledge to MAKE IT do are two different things.

    Will you please help me with the last piece of this feature.

    I have a frm (frmDirectoryRpt) with a tab control on it. This form will need to be opened from the mainform (frmTARGET).

    The problem that I have is, instead of entering in NEW request, in the past, the Reps have altered existing one. This doesn't work for countless reasons and has been a thorn in my side. When the Rep opens frmDirectoryRpt to submit a new request, I don't mind if they can view prior requests but I don't want them to be able to change them.

    Each tab has a frm on it (i.e. sfrmDir_SplitEv) with a subform based off of a table (i.e. tblDirectoryCodes) in Datasheet view.

    Every time frmDirectoryRpt opens up it has to be linked to the JDE field on frmTARGET (JDE to JDE) but I don't want them to be able to make changes. I've trying opening frmDirectoryRpt with a cmd button, with OnClick set to a macro, opening the form in Add data Mode but then I lose the link to the JDE on frmTARGET. I tried to figure out the filter part of the macro, thinking that's might make it work but we didn't cover that in class and I just can't get it. I tried to set the Allow Edits and Allow Deletions to No on the frm but then it won't give me a new record either.

    I'm too frustrated to type anymore. Does any of this make sense and can you offer any suggestions. Someone tried to help me with the link thing the other day from the lounge but they went way over my head and I tried to research the suggestions that they made and just got more lost and confused.

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

    Re: If Statement (Access 2000)

    I'm afraid I don't understand your description - if you open a form to a new record, how can it be linked to a field (JDE) in another form?

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

    Re: If Statement (Access 2000)

    When the attached form is opened, I would like to allow the Rep to view previous request but only be able to enter a new one.

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

    Re: If Statement (Access 2000)

    Please forgive me for being stupid, but I still don't understand. In your previous reply, you mentioned that frmDirectoryRpt was to be opened from frmTarget, the latter is not present in the database you attached.

    Where should the user enter a new record? In frmDirectoryRpt or in one of the subforms?

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

    Re: If Statement (Access 2000)

    We allow members to SPLIT product with another member and code them both as carrying it. In order to submit the request to the Directory dept. we need to provide them with the member number of the purchasing shop or they won't process it.

    I had a msg pop up which reminded the Rep that they had to fill in the purchasing shop #, if it was blank. Now that it's on the tab control, it stopped working. Would you mind looking at that for me also. I sent the attachment over on the last post.

    Thank you SO MUCH.

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

    Re: If Statement (Access 2000)

    It was too big to attach so I stripped it down.

    Hey, I have an idea. If it can't be set up so that they can not change something that was previously submitted on the tab control, can you tell me how to write a different code.

    What I'm thinking is, if they try to type in a field that already has data in it, they could get a pop up msg "HANDS OFF BUDDY" (lol) and when they close the pop up, force the cursor into some other field or a blank text box. Just a thought, I'm drawing at straws.

    It would be great if the first record on each tab was a new record so they would start with the one that they are going to fill in instead of a previous record.

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

    Re: If Statement (Access 2000)

    I couldn't reply to your last inquiry. Yes, I tried to attach the database with frmTARGET on it but it was too big.

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

    Re: If Statement (Access 2000)

    You could use this code in the On Current event of frmDirectoryRpt:

    Private Sub Form_Current()
    On error resume next
    Me.sfrmDir_Add.SetFocus
    RunCommand acCmdRecordsGoToNew
    Me.sfrmDir_Block.SetFocus
    RunCommand acCmdRecordsGoToNew
    Me.sfrmDir_RecodeEv.SetFocus
    RunCommand acCmdRecordsGoToNew
    Me.sfrmDir_RecodeHol.SetFocus
    RunCommand acCmdRecordsGoToNew
    Me.sfrmDir_SplitHol.SetFocus
    RunCommand acCmdRecordsGoToNew
    Me.sfrmDir_SplitEv.SetFocus
    RunCommand acCmdRecordsGoToNew
    End Sub

Page 1 of 2 12 LastLast

Posting Permissions

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