If Statement (Access 2000)

Topic

New Reply

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

Reply | Quote

Replies

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
#-####LL;0;_
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.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

Perfect – thanks again.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

That’s great!!!

Don’t hate me!!! Can you help me with this part too: 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.

If I know how it’s supposes to be set up for one or two, I can copy and change the filed names for the others.

Reply | Quote

What happens if you set the Allow Edits property of the subforms to No?

Reply | Quote

With Allow Edits = No, I can no longer select an item or option on sfrmDirSplitEv, even though I’m trying to ADD a new record to the tab.

Reply | Quote

Try this in the On Current event of the subforms:

Private Sub Form_Current()
Me.AllowEdits = Me.NewRecord
End Sub

Allow Edits will only be set to Yes in a new record.

Reply | Quote

It worked on all but the Split-Everyday tab. I don’t see anything different between that one and the other ones though.

Reply | Quote

It works when I try it in the database you attached. What goes wrong in your copy? Can you still edit, or can’t you add a record, or what?

Reply | Quote

I found it – sorry about that!

Before you sent over the code to open to a new record, I had put a cmdButton directly on that form to try and work around it. I’ve taken the button off now it’s fine.

Oh Happy Days! Thank you SO MUCH for the support!!!!

Reply | Quote

OK, thanks. (We posted at about the same time, so the replies crossed each other)

Reply | Quote

Hans – I can’t thank you enough!

Thank you, thank you, thank you.

Reply | Quote

I found it – sorry about that!

Before you sent over the code to open to a new record, I had put a cmdButton directly on that form to try and work around it. I’ve taken the button off now it’s fine.

Oh Happy Days! Thank you SO MUCH for the support!!!!

Reply | Quote

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

Reply | Quote

Sorry about that, I stupidly thought that you intended to attach a NEW file, then realized you meant the file you had already attached. So I deleted my reply.

Reply | Quote

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.

Reply | Quote