2 Forms & 1 Table (1 Viewer)

Mick3911

Registered User.
Local time
Today, 16:36
Joined
Jul 31, 2018
Messages
40
Hi,

I have a table with 15 fields

The first 5 fields are generic (including the KeyID)

The next 5 fields relate to a particular scenario and the remaining 5 fields relate to another scenario.

I have created 2 forms for the 2 different scenarios with the fist 5 fields being the same. The forms are set to go to new record when loaded.

I can open Form 1 with no problem but cannot open Form 2.

I’m guessing it is because both forms are using the same KeyID.

Is there a way to get around this?

I have tried using 2 subforms but it looks really messy. Is it possible for both subforms to be invisible and on clicking on a command button, open the subform that is required?

Any creative suggestions would be greatly appreciated.

Many thanks
 

isladogs

MVP / VIP
Local time
Today, 16:36
Joined
Jan 14, 2017
Messages
18,186
It's very easy to make a subform visible. In your button click code just type
Code:
me.subform1.visible=true
Where subform1 is the name of the control holding a subform.
 

plog

Banishment Pending
Local time
Today, 11:36
Joined
May 11, 2011
Messages
11,611
I can open Form 1 with no problem but cannot open Form 2.

That doesn't make a lot of sense. I mean, the forms are effectively independent, right? If their data is Scenario1, they open/use Form 1. If Scenario 2 they open/use Form 2. There should be no KeyID present when either of those forms open.

I would use just 1 form for this. They fill out the first 5 fields, then hide/unhide inputs for the corresponding scenario.
 

Mick3911

Registered User.
Local time
Today, 16:36
Joined
Jul 31, 2018
Messages
40
That works great isladogs, many thanks.

My next predicament is;
Say I originally opened the main form (Form 1) then opened subform 1 and completed the fields.

I then want someone else to open another form (Form 2) (from a hyperlink in a datasheet which will open the record selected) which will have the details from Form 1 as well as some other fields for them to complete but I would like the form to the subform that I originally completed (subform 1).
Is it possible to open Form 2 with the subform that I originally completed?

Hope that makes sense.
Cheers
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:36
Joined
Oct 29, 2018
Messages
21,358
Hi. Would you mind telling us what your database is all about? Why would scenario 1 and 2 have the same keyID?
 

Mick3911

Registered User.
Local time
Today, 16:36
Joined
Jul 31, 2018
Messages
40
The database is for recording Non-conformances (Internal & External).

Depending on if the non-conformance is Internal or External depends on the information/data required.

I have already set up 2 different DB's but the Boss wants them all in one with the 'KeyID' running consecutively.
 

isladogs

MVP / VIP
Local time
Today, 16:36
Joined
Jan 14, 2017
Messages
18,186
Say I originally opened the main form (Form 1) then opened subform 1 and completed the fields.

I then want someone else to open another form (Form 2) (from a hyperlink in a datasheet which will open the record selected) which will have the details from Form 1 as well as some other fields for them to complete but I would like the form to the subform that I originally completed (subform 1).
Is it possible to open Form 2 with the subform that I originally completed?

Hope that makes sense.
Cheers

Sounds a bit convoluted but assuming its a split db and each user has their own copy of the FE, then yes its definitely possible.

Any reason why the other user can't just open the same form as you (Form1)?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:36
Joined
Oct 29, 2018
Messages
21,358
The database is for recording Non-conformances (Internal & External).

Depending on if the non-conformance is Internal or External depends on the information/data required.

I have already set up 2 different DB's but the Boss wants them all in one with the 'KeyID' running consecutively.
Hi. Thanks for the additional information. You can definitely use one DB but you may also be able to restructure your table to avoid storing records with a bunch of empty fields. Just food for thought...
 

Mick3911

Registered User.
Local time
Today, 16:36
Joined
Jul 31, 2018
Messages
40
Any reason why the other user can't just open the same form as you (Form1)?

I don't want the other user to have access to editing the original data as 9 times out of 10 the non-conformance will be about them.

They just need to enter data saying why it happened and whats been done about it.
 

isladogs

MVP / VIP
Local time
Today, 16:36
Joined
Jan 14, 2017
Messages
18,186
That's not a problem if you assign user permission levels.
For example:
Level 1 - user (read only access)
Level 2 - editor (able to edit some items)
Level 3 - admin (edit all items)

Then if someone is on level 2, selected controls are locked or hidden or disabled (whichever you prefer). The idea can be used throughout your application and means you don't need to make two versions of each form.
 

Mick3911

Registered User.
Local time
Today, 16:36
Joined
Jul 31, 2018
Messages
40
That would defeat my original issue where every field will be on a form which would look rather messy.

Is there a way to view only the subform that has data similar to only viewing a subreport that has data?
 

isladogs

MVP / VIP
Local time
Today, 16:36
Joined
Jan 14, 2017
Messages
18,186
That would defeat my original issue where every field will be on a form which would look rather messy.

Is there a way to view only the subform that has data similar to only viewing a subreport that has data?

It is a very common way of managing this type of situation and FAR better than duplicating forms
It doesn't need to look messy if you hide all controls the user can't edit

You can hide subforms with no data by doing something like this:

Code:
If DCount("*","qryMyRecordSource")=0 Then Me.SubformControlName.Visible=False

where qryMyRecordSource is the query (or table) used as the subform record source
 

Mick3911

Registered User.
Local time
Today, 16:36
Joined
Jul 31, 2018
Messages
40
Hi isladogs,

Please excuse my ignorance but where would I place the code? On the form containing the 2 subforms under 'On Load'?
 

isladogs

MVP / VIP
Local time
Today, 16:36
Joined
Jan 14, 2017
Messages
18,186
Please excuse my ignorance but where would I place the code? On the form containing the 2 subforms under 'On Load'?

Exactly that.

Not totally relevant but you may find the attached example useful.
It shows how you can move selected controls up on a form if you hide a subform
In the example, a button click if used.
If you like the idea, you would use similar code in the IF ...End If code section in the Form_Load event
 

Attachments

  • SubformShrink&Grow_v3.zip
    54.7 KB · Views: 34

Mick3911

Registered User.
Local time
Today, 16:36
Joined
Jul 31, 2018
Messages
40
Hi isladogs,
I am having problems display just the one subform containing data.
This is the code that I have used when the main form loads.
Private Sub Form_Load()

If DCount("*", "tblNCRDetails") = 0 Then
Me.subfrm1.Visible = False

Else

If DCount("*", "tblNCRDetails") = 0 Then
Me.subfrm2.Visible = False

End If

End If

End Sub

I am pulling my hair out here :confused: Where am I going wrong?
 

isladogs

MVP / VIP
Local time
Today, 16:36
Joined
Jan 14, 2017
Messages
18,186
3 issues:
1. You have two End If statements
2. You have used the same DCount for each part which won't work.
3. Make sure subfrm1 & subfrm2 are the names of the main form controls containing the subforms

Here's a slightly different way of doing it:

Code:
Private Sub Form_Load()

Me.subfrm1.Visible = False
Me.subfrm2.Visible = False

If DCount("*", "tblNCRDetails","some condition goes here") > 0 Then
    Me.subfrm1.Visible = True
ElseIf DCount("*", "tblNCRDetails", "some other condition goes here") > 0 Then
   Me.subfrm2.Visible = True
End If

End Sub
 

Mick3911

Registered User.
Local time
Today, 16:36
Joined
Jul 31, 2018
Messages
40
Hi isledogs,

Appreciate you helping me out with this.

I am still having problems opening the respective subform.

I raise a new non-conformance by clicking on the ‘Raise A New NCR’ button on the Home Page, fill in the data and depending if it relates to an Internal or External NCR, click on the corresponding button.

This then displays the appropriate subform. After filling the data, a report is sent by e-mail (not Contained in this DB).

Internal NCR – On this example DB, the Packing Manager would receive an e-mail with a copy of the report attached. He then opens his ‘Packing Management’ form with lists NCRs that he needs to address as a datasheet.
He then clicks on the Hyperlink which opens the Internal Response form for him to fill out his response.

External NCR – Similar to the Internal NCR, an e-mail is sent with a copy of the report to the Supplier. They reply with their response where someone would then open the External NCR Response form and copy & paste the Suppliers’ response.

The problem I am having is that the corresponding subform is not opening when the NCR Response form is opened/loaded.

I have uploaded a very very slimmed down version of the DB I am working on with the last VBA code that you suggested. I would be grateful if you would have a look and see where I am going wrong.

As you can properly tell, VBA is not one of my strongest points.
Regards
 

isladogs

MVP / VIP
Local time
Today, 16:36
Joined
Jan 14, 2017
Messages
18,186
Nothing attached. As you have less than 10 posts you need to zip your file
 

Mick3911

Registered User.
Local time
Today, 16:36
Joined
Jul 31, 2018
Messages
40
DB now attached:rolleyes:
 

Attachments

  • Database1.zip
    181.4 KB · Views: 39

isladogs

MVP / VIP
Local time
Today, 16:36
Joined
Jan 14, 2017
Messages
18,186
several issues:

1. You MUST reference the control on the main form that contains each subform and NOT the name of the subform itself

In your case that means omitting the Response part in the code
Me.subfrmExternalNCR.Visible = False instead of Me.subfrmExternalNCRResponse.Visible = False

2. Next problem is the two lines in red which need to be different.
If they are the same, both subforms will be visible or hidden
The DCount also needs to have a condition
"txtDepartment.HasData" is a field name in a table - not a condition

Code:
Option Compare Database
Option Explicit [COLOR="SeaGreen"]'NEED THIS ON EVERY CODE MODULE[/COLOR]

Private Sub Form_Load()

Me.subfrmExternalNCR.Visible = False
Me.subfrmInternalNCR.Visible = False [COLOR="Blue"] '<<I just corrected an error here[/COLOR]

[COLOR="Red"]If DCount("*", "tblNCRDetails", "txtSupplier.HasData") > 0 Then[/COLOR]
    Me.subfrmExternalNCR.Visible = True
[COLOR="red"]ElseIf DCount("*", "tblNCRDetails", "txtDepartment.HasData") > 0 Then[/COLOR]
   Me.subfrmInternalNCR.Visible = True
End If

End Sub

3. Also note the part marked in green
As you now have Require Variable Declaration ticked, this will be added to all new code modules.
However you need to add it to all existing code modules then run Debug...Compile from the VBE
The file you sent has at least one compile error you need to fix.
Your full file will probably have more
 
Last edited:

Users who are viewing this thread

Top Bottom