Show and hide sub form (1 Viewer)

Gismo · Jul 25, 2017

Hi all, I have an Issue with autonumber being generated before I have accepted the input from other fields. when cancelled I loose a autonumber. So I have decided to use a separate table just for order initializing and when accepted it would populate to orders table to generate the auto number. doing so I have both subforms loaded on top of each other, the main form displays when I open the form but when I click new, the main form must be hidden then display the secondary form must be displayed. below is my code but not doing as require, sure my coding is all wrong. Please could you assist?

Private Sub NewOrder_Click()
If PurchaseOrderSubForm.Visible
Me.PurchaseOrderSubFormNew.Visible = True
Me.PurchaseOrderSubForm.Visible = False
Me.PurchaseOrderSubFormNew.SetFocus
Else
Me.PurchaseOrderSubFormNew.Visible = False
Me.PurchaseOrderSubForm.Visible = True
End If
End Sub
End Sub

isladogs · Jul 25, 2017

I have an Issue with autonumber being generated before I have accepted the input from other fields

This follows on from an earlier thread
https://www.access-programmers.co.uk/forums/showthread.php?t=294882

In that thread, various experienced forum members strongly advised you against this approach making it clear that it would cause you problems.

You have now experienced one such problem.
Even if you solve this one, other problems will occur later.

The best solution would be to abandon creating 'autonumbers' in this way.

However, the following MAY solve this particular issue for you depending on whether the New_Order button is in the main form or the subform

Code:

Private Sub NewOrder_Click()

If Me.PurchaseOrderSubForm.Visible = True Then
     Me.PurchaseOrderSubFormNew.Visible = True
     Me.PurchaseOrderSubFormNew.SetFocus
     Me.PurchaseOrderSubForm.Visible = False
Else
     Me.PurchaseOrderSubFormNew.Visible = False
     Me.PurchaseOrderSubForm.Visible=True
End If

End Sub

Your errors included:
a) You missed out the 'Then' in the If line
b) you had 2 End Subs
c) you must set focus to the other subform before you can hide the one which has the focus

NOTE: If that doesn't work, you may need to reference the subform controls as part of the main form

Gismo · Jul 25, 2017

1. How would you suggest creating autonumbers in any other way to allocate an automatic system generated batch number for each stock item received? for now the autonumber is not a problem but I hear what you all say about future probles
2. My code above is my solution to get by the problem if someone could help me with the correct coding as i am sure I have same syntax errors in there

isladogs · Jul 25, 2017

Gismo said:
1. How would you suggest creating autonumbers in any other way to allocate an automatic system generated batch number for each stock item received? for now the autonumber is not a problem but I hear what you all say about future probles
2. My code above is my solution to get by the problem if someone could help me with the correct coding as i am sure I have same syntax errors in there

1. Like those who responded in the earlier thread, I would use an autonumber field and not worry about whether there are some resultant gaps in the numbering.
Its whole purpose is to create a UNIQUE ID - the actual value isn't important

2. I just gave you the correct coding with the errors fixed!

Gismo · Jul 25, 2017

what Would the code be to reverse the hide / unhide subforms? Turn back the focus to main form from the secondary hidden form?
PurchaseOrderSubForm = main form, on focus when form opened
PurchaseOrderSubFormNew = secondary form (hidden on form open) after update this form must be hidden again

Private Sub NewOrder_Click()

If Me.PurchaseOrderSubForm.Visible = True Then
Me.PurchaseOrderSubFormNew.Visible = True
Me.PurchaseOrderSubFormNew.SetFocus
Me.PurchaseOrderSubForm.Visible = False
Else
Me.PurchaseOrderSubFormNew.Visible = False
Me.PurchaseOrderSubForm.Visible=True
End If

End Sub

Minty · Jul 25, 2017

As Ridders and others have said - you are leaping through hoops here caused by your own making.
Your forms appear to me to be working (Excuse the phrase) "arse about face". In other words you appear to be trying to create child records before you have created a master record. This is always going to cause you immense amounts of additional work and cludges.

Take a step back and look at your tables, what you are trying to record and what you want out of the system.

A simple order/invoice system should not be this difficult to get the basics working.

Explain to us in simple terms what your system is trying to accomplish.
From the tables I saw in an earlier example db you uploaded they didn't look quite right to me.

Gismo · Jul 25, 2017

I laymans terms, I need to use the auto number in my orders table as purchase order number. to keep the integrity of auto numbers and not to loose any numbering incase an order is not completed, I input details in a separate table, when user accept the order header, I populate that into the actual orders table to take on the numbering system. I use the 2 sub forms to toggle between orders table and makeshift orders table. seems to be working quite well at this stage. just not sure how to toggle back to main form from the code above to indicate the actual order number for the user to access

Minty · Jul 25, 2017

This puzzles me

I need to use the auto number in my orders table as purchase order number.

Why ? What happens if you order something for stock - that you don't have an existing order for?

An order from a customer should be treated as a separate item from your purchasing system. By all means add a junction table storing if a PO was for specific order(s) but surely they can't be absolutely directly related? If they are I would think it is a pretty unusual business model.

isladogs · Jul 25, 2017

Gismo said:
I laymans terms, I need to use the auto number in my orders table as purchase order number. to keep the integrity of auto numbers and not to loose any numbering incase an order is not completed, I input details in a separate table, when user accept the order header, I populate that into the actual orders table to take on the numbering system.

No you don't need to do this.
Use the AutoNumber field in the way it is intended (see previous posts)
Create an order number as a separate field which you increment by 1 each time an order is completed

I use the 2 sub forms to toggle between orders table and makeshift orders table. seems to be working quite well at this stage. just not sure how to toggle back to main form from the code above to indicate the actual order number for the user to access

You've got to 72 posts without ever thanking anyone.
That's despite the fact that in most cases they have done almost all the work for you. I gave you the last bit of code which you reposted without acknowledgement.
Suggest you try reversing the sequence in order to do the reverse steps.
You never know - you might do it without help. Think how satisfying that would be

Gismo · Jul 25, 2017

Minty, All order details populates to my stock detail table to have all my stock in one table. Just the orders number I need to have for history purposes.

Ridders, I have not even though of incrementing the last number to be honest, I will most definitely implement that.
Sorry Ridders I do thank, just did not know I had do it through the system, I do appologise for that. just noticed the thumbs up on the right. Sorry everyone

isladogs · Jul 25, 2017

Gee thanks ... you're certainly on a roll now with the Thumbs button .... but don't overdo it ....

Show and hide sub form (1 Viewer)

Gismo

Registered User.

isladogs

MVP / VIP

Gismo

Registered User.

isladogs

MVP / VIP

Gismo

Registered User.

Minty

AWF VIP

Gismo

Registered User.

Minty

AWF VIP

isladogs

MVP / VIP

Gismo

Registered User.

isladogs

MVP / VIP

Similar threads

Users who are viewing this thread