Make a text box appear on some records but not all. (1 Viewer)

Hi all,

I am self taught on Access so I apologise if the answer is simple. I do feel like there is something simple I am overlooking.
I am trying to add more part numbers for one particular supplier (account_ref). This is the only supplier (ARB20) I may require more than one part number on.
What I am trying to say is once "Part_Number" has been entered, prompt me if I want to add another part number BUT only prompt when "Account_Ref" = "ARB20".
Then if answer=yes make "Part_Number_2" visible.
If answer=no then do nothing.
This works when I enter it all on an existing record with Account_Ref = ARB20
However when I then go to the next record where Account_Ref DOES NOT = ARB20 , Part_Number_2 is visible. How do I stop it being visible on this other record?

Here's my code:

Private Sub Part_Number_AfterUpdate()

If Me.Account_Ref = "ARB20" Then

Dim answer As String

answer = MsgBox("Do you need to enter another part number?", vbYesNo, "Another Part Number?")

If answer = vbNo Then
'donothing
Else
Me.Part_Number_2.Visible = True

End If

End Sub

Private Sub Part_Number_2_AfterUpdate()

If Me.Account_Ref = "ARB20" Then


Dim answer As String

answer = MsgBox("Do you need to enter another part number?", vbYesNo, "Another Part Number?")

If answer = vbNo Then
'donothing
Else
Me.Part_Number_3.Visible = True

End If

End Sub

Private Sub Part_Number_3_AfterUpdate()

If Me.Account_Ref = "ARB20" Then


Dim answer As String

answer = MsgBox("Do you need to enter another part number?", vbYesNo, "Another Part Number?")

If answer = vbNo Then
'donothing
Else
Me.Part_Number_4.Visible = True

End If

End Sub

Any suggestions are much appreciated and thank you in advance.

Use Me.Part_Number_2.Visible = False as appropriate

TobyMace said:

Hi all,

I am self taught on Access so I apologise if the answer is simple. I do feel like there is something simple I am overlooking.
I am trying to add more part numbers for one particular supplier (account_ref). This is the only supplier (ARB20) I may require more than one part number on.
What I am trying to say is once "Part_Number" has been entered, prompt me if I want to add another part number BUT only prompt when "Account_Ref" = "ARB20".
Then if answer=yes make "Part_Number_2" visible.
If answer=no then do nothing.
This works when I enter it all on an existing record with Account_Ref = ARB20
However when I then go to the next record where Account_Ref DOES NOT = ARB20 , Part_Number_2 is visible. How do I stop it being visible on this other record?

Click to expand...

which you want to make invisible set the visible property to false

However, the fact that you have _2,_3 & _4 for your part numbers would appear to indicate your structure is incorrect.?
If you just had a PartNumber then you would only have the code in one place, plus have PartNumbers linked to the Account_Ref ?

VbNo isn't a string, it's a VB constant that is an integer value, so I'd be very surprised if
is ever evaluated correctly.

But as Gasman explained your structure is wrong, and will get you into all sorts of problems as you develop this further.
You should have a separate table for parts and relate it back to your main record ID.

TobyMace said:

...when I then go to the next record where Account_Ref DOES NOT = ARB20 , Part_Number_2 is visible. How do I stop it being visible on this other record...

Click to expand...

You'll need to do the same check, for the value of account_ref, and if it is ARB20 set the visibility of the extra 'Part_Number Textboxes,' if they contain data accordingly.

But as has been said, this is a really bad approach, unless you're absolutely, positively, without a doubt sure that you'll only ever have a finite set of Part-Numbers! And even then, Relational Database rules dictate that you should have a separate Table for these Numbers, related to the Main Table via the Primary Key of that Main Table...and they should be displayed as a Subform of the Main Table's Form.

Linq ;0)>

I thank you all for your replies.
I don't know whether explaining the situation will help for a bit of context...
This is on a form for rejected items which contains a large amount of information about the supplier, dates, various department involvement etc. So we would usually only be rejecting one item from one supplier each time (on the odd occasion two different items with the same but we filled out the form twice and changed the part number). But one new unique supplier has tough tolerances and we have more rejects than usual and filling out each form for 4 or 5 different items can be time consuming. If it's the same item we obviously have a quantity field but the issue is different items.
So I understand the formatting isn't ideal however this is the best idea I could come up with for adding to an existing, extensive database (what I consider extensive anyway).
I work in the electronics industry so the number of part numbers are in the 10,000's nearing the 100,000's. A separate table doesn't seem a simple solution, although looking like the only.
There are many things that the original designer had not included as there was no requirement for it. As my company has grown and more aspects included, adjustments need to be made. I have carried out various updates successfully.
Thanks again for all your help unfortunately none seem to have worked.

I deal with parts for electronic repairs. One supplier had over 130,000 part in their catalog, and that wasn't an issue for Access to deal with.

We used the master parts list to select part numbers by manufacturer or supplier and it wasn't slow. We used a couple of combo boxes and simply didn't load all the records, just a sub set based on the first 4 or 5 part characters of the part number.

But that isn't what is being suggested here. Think of it like a order form, your supplier details is your order header and the part returned is your order line. That is the sub table .

Your code seems to suggest you have a single table containing the supplier with each record having the parts for that supplier. As has already been suggested, this is not the right design. The parts should be in a separate table, related on the SupplierID. What happens after Part_Number_99, Part_Number_999 etc?

As to the matter of the message box, in your code
answer should have been declared as a numeric variable.

Incidentally vbYes has the value 6 and vbNo has the value 7, not True/False

I think the "Part_Number_2" etc is throwing people off. I don't have Part_Number_999 for one supplier.
I have a separate record for each reject.
Each reject record has the supplier and the part number recorded.
Then we move onto another reject form that will have the same supplier and same part number at a later date or on another order number.
The fields "Part_Number_2", "Part_Number_3" is purely for each reject record. So the maximum I want is "Part_Number_4" for one reject.
Each reject record will have 4 part numbers maximum but only for the supplier "ARB20".
Having a separate table won't help me because I still need the entry boxes to only appear for "ARB20" and not appear for other suppliers.

So ignoring the structure issue, did changing the datatype of your form msgbox answer sort things out for your logic flow ?

Eg.

No luck I'm afraid.
The next text box will appear on the "ARB20" records but is still there on other supplier records.

I have solved it!

Private Sub Part_Number_AfterUpdate()

If Me.Account_Ref = "ARB20" Then

Dim answer As Integer

answer = MsgBox("Do you need to enter another part number?", vbYesNo, "Another Part Number?")

If answer = 6 Then
Me.Part_Number_2.Visible = True
Else
Me.Part_Number_2.Visible = False
End If
End If

End Sub

Private Sub Part_Number_2_AfterUpdate()

If Me.Account_Ref = "ARB20" Then

Dim answer As Integer

answer = MsgBox("Do you need to enter another part number?", vbYesNo, "Another Part Number?")

If answer = 6 Then
Me.Part_Number_3.Visible = True
Else
Me.Part_Number_3.Visible = False
End If
End If

End Sub

Private Sub Part_Number_3_AfterUpdate()

If Me.Account_Ref = "ARB20" Then

Dim answer As Integer

answer = MsgBox("Do you need to enter another part number?", vbYesNo, "Another Part Number?")

If answer = 6 Then
Me.Part_Number_4.Visible = True
Else
Me.Part_Number_4.Visible = False
End If
End If

End Sub

Private Sub Form_Current()

If Me.Account_Ref <> "ARB20" Then
Me.Part_Number_2.Visible = False
Me.Part_Number_3.Visible = False
Me.Part_Number_4.Visible = False
End If

If Not IsNull(Me.Part_Number_2) Then
Me.Part_Number_2.Visible = True
End If
If Not IsNull(Me.Part_Number_3) Then
Me.Part_Number_3.Visible = True
End If
If Not IsNull(Me.Part_Number_4) Then
Me.Part_Number_4.Visible = True
End If

End Sub

I thank you all for your help and patience! I apologize if I didn't understand what you were trying to explain!:banghead:

Best regards!

You can simplify that quite a bit

On Form Current simply use


Which also means on all your after update events you only need


As it will already be not visible when the check is run.

Thanks Minty!
Best regards.