I'm missing something through lack of knowledge (1 Viewer)

Sam Summers

Registered User.
Local time
Today, 16:15
Joined
Sep 17, 2001
Messages
939
Hi again,

I'm trying to open a form from a button with the following code but getting the error as attached

Code:
Private Sub TOEnterByAreaBtn_Click()
On Error GoTo TOEnterByAreaBtn_Click_Err

    Dim stDocName As String
    Dim stLinkCriteria As String
    
        If IsNull(SelectArea) Then
        DoCmd.Beep
        MsgBox "You must select an Area first.", vbExclamation, "No Area selected!"
        End
    End If

    ClientName.SetFocus
    stLinkCriteria = "[SetArea]=" & Me![SelectArea] & " And [ReportNumber] = " & Me![ReportNo]
    If Forms!Main!ClientName = "TRA" Then
    stDocName = "EnterItemTransocean"
    Else
    stDocName = "EnterItemDROPS"
End If
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    
TOEnterByAreaBtn_Click_Exit:
    Exit Sub

TOEnterByAreaBtn_Click_Err:
    MsgBox Error$
    Resume TOEnterByAreaBtn_Click_Exit
    
End Sub

'SetArea' is an integer 1 to 5 and is a combobox

I'm guessing there is something wrong with my LinkCriteria statement?

Thank you in advance
 

Attachments

  • Screenshot_1.png
    Screenshot_1.png
    1.9 KB · Views: 88

Isskint

Slowly Developing
Local time
Today, 16:15
Joined
Apr 25, 2012
Messages
1,302
Hi

The 'error' is actually a prompt box ot enter a parameter, so it does not understand SetArea as a defined field. Try me.SetArea.
You mention SetArea is an integer but is SelectArea a number?
Is the format of SetArea combobox set as a number?
 

Minty

AWF VIP
Local time
Today, 16:15
Joined
Jul 26, 2013
Messages
10,354
You are still not quite getting the referencing correct. You should always use Me.YourControl to reference a control on the form you are on. Don't use Me! as it changes the type of reference.
Code:
Private Sub TOEnterByAreaBtn_Click()
On Error GoTo TOEnterByAreaBtn_Click_Err

    Dim stDocName As String
    Dim stLinkCriteria As String
    
    If IsNull([COLOR="Red"]Me.[/COLOR]SelectArea) Then
	DoCmd.Beep
	MsgBox "You must select an Area first.", vbExclamation, "No Area selected!"
	Exit Sub
    End If

    ClientName.SetFocus
    stLinkCriteria = "[SetArea]=" & [COLOR="Red"]Me.[/COLOR][SelectArea] & " And [ReportNumber] =[COLOR="red"] '[/COLOR]" & [COLOR="red"]Me.[/COLOR][ReportNo] [COLOR="red"]& "'"[/COLOR]
    If Forms!Main!ClientName = "TRA" Then
    	stDocName = "EnterItemTransocean"
    Else
    	stDocName = "EnterItemDROPS"
    End If
[COLOR="red"]    Debug.Print stLinkCriteria[/COLOR]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    
TOEnterByAreaBtn_Click_Exit:
    Exit Sub

TOEnterByAreaBtn_Click_Err:
    MsgBox Error$
    Resume TOEnterByAreaBtn_Click_Exit
    
End Sub
I've added the single quotes back around your report name as it's a string from your previous posts. And I've added a debug so you can see exactly what it is you are trying to use as criteria.
Also I assume [SetArea] is actually a field in your report not a combo box....?
 

Sam Summers

Registered User.
Local time
Today, 16:15
Joined
Sep 17, 2001
Messages
939
Thanks guys,

I have never had these problems before so I am baffled!???

Tried your suggestions but its still doing the same?

SetArea is a comboBox on the main form but I have just realised part of the reason or all of the reason?

I changed the form to a form with a subform 'TOItem_Subform' so it is not correctly referring to it I guess?
 

Minty

AWF VIP
Local time
Today, 16:15
Joined
Jul 26, 2013
Messages
10,354
What did Debug.Print come up with in the immediate window ?
Are both [SetArea] And [ReportNumber] available on the form you are trying to open ?
 

Sam Summers

Registered User.
Local time
Today, 16:15
Joined
Sep 17, 2001
Messages
939
This is not going to be as easy as I thought because there are two different forms as you can see in the code but also each form also has a differently named Subform so the LinkCriteria will be different for each?

If the form being opened is "EnterItemTransocean" then its Subform is "TOItem_Subform.

and for "EnterItemDROPS" the subform is "DROPSItem_Subform"

Also the item being referenced on the subform is 'Area' not 'SetArea'

The code I currently have is:-

Code:
Private Sub TOEnterByAreaBtn_Click()
On Error GoTo TOEnterByAreaBtn_Click_Err

    Dim stDocName As String
    Dim stLinkCriteria As String
    
        If IsNull(Me.SelectArea) Then
        DoCmd.Beep
        MsgBox "You must select an Area first.", vbExclamation, "No Area selected!"
        End
    End If

    ClientName.SetFocus
    stLinkCriteria = "[[Area]=" & Me.[SelectArea] & " And [ReportNumber] = '" & Me.[ReportNo] & "'"
    If Forms!Main!ClientName = "TRA" Then
    stDocName = "EnterItemTransocean"
    Else
    stDocName = "EnterItemDROPS"
End If
    Debug.Print stLinkCriteria
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    
TOEnterByAreaBtn_Click_Exit:
    Exit Sub

TOEnterByAreaBtn_Click_Err:
    MsgBox Error$
    Resume TOEnterByAreaBtn_Click_Exit
    
End Sub
 

Minty

AWF VIP
Local time
Today, 16:15
Joined
Jul 26, 2013
Messages
10,354
Comment out your error handling at the beginning of the code. When you get your error come up you get the option to debug. Have a look at what come up.

You shouldn't be setting a criteria on your sub form - your parent form should be doing that when it opens. Sub forms are normally (but not always) automatically linked to the form they are in.

Fancy posting up the current version of your db again?
 

Sam Summers

Registered User.
Local time
Today, 16:15
Joined
Sep 17, 2001
Messages
939
Ok, so maybe I should put an unbound textbox on the main form that the subform can reference on opening?

Here is my DB so far as attached
 

Attachments

  • DROPS.zip
    456.4 KB · Views: 80

namliam

The Mailman - AWF VIP
Local time
Today, 17:15
Joined
Aug 11, 2003
Messages
11,696
stLinkCriteria = "[[Area]=" & Me.[SelectArea] & " And [ReportNumber] = '" & Me.[ReportNo] & "'"

Try removing the surplus bracket
 

Minty

AWF VIP
Local time
Today, 16:15
Joined
Jul 26, 2013
Messages
10,354
I've taken a better look at your data and structures and I can see you have got yourself in a confused state about how to go about your (current) two types of report.

Take a step back and look at your reports table and your items tables.

Are you ever likely to have a third or 4th or 10th type of report / customer? If so I would strongly suggest a different data model. If you have another report you will have to add another set of forms / reports / buttons etc...

I would be tempted to one of two things ;
1) The correct way would be to create a table called ReportFields with fields along the lines of CustomerCode, FieldName & Datatype. You would use this to populate a form dynamically, and then store that customers data in a customer specific data table. This is a complicated way of dealing with multiple data types for multiple customers. I would be very wary of trying to accomplish this at your Access skill level. Or mine :) .

2) If you really are only dealing with the two customers and this is really unlikely to change, I would simply have all the required report fields in one table. Use one form for the data entry and simply hide / show the relevant fields based on which customer it is for. You can do the same for the logo, titles etc. etc. on all the underlying forms if it helps ensure the end user knows who they are talking to.

When the report is finalised you create two reports (as i suspect you already have) one for each customer and you are done.

The second approach is NOT very normalised data friendly, and to be honest the first one isn't either strictly speaking, but storing data types dynamically is extremely complicated.
 

Sam Summers

Registered User.
Local time
Today, 16:15
Joined
Sep 17, 2001
Messages
939
Thanks NamLiam I will try that.

Minty - We will only ever have two different types of input and 2 or maybe 3 types of reports.

So I have duplicated the DB and am currently trying what you suggested and I will post the outcome and maybe any problems I come across in the course of doing this.

So I think your 2nd option will potentially be the answer?
 

Sam Summers

Registered User.
Local time
Today, 16:15
Joined
Sep 17, 2001
Messages
939
I did that NamLiam.

I'm still getting this message as attached and I just don't understand why?!!!!
 

Attachments

  • Screenshot_1.png
    Screenshot_1.png
    1.8 KB · Views: 78

namliam

The Mailman - AWF VIP
Local time
Today, 17:15
Joined
Aug 11, 2003
Messages
11,696
Area is an unbound field, don't really think filtering on that is possible or even usefull.
 

Sam Summers

Registered User.
Local time
Today, 16:15
Joined
Sep 17, 2001
Messages
939
Wow, I will have to scrap the whole DB because it cant sort on a simple field?

That's annoying:(
 

Sam Summers

Registered User.
Local time
Today, 16:15
Joined
Sep 17, 2001
Messages
939
Now I am getting the error message as attached - obviously due to not fully grasping the syntax of the stLinkcriteria?

Code:
Private Sub TOEnterByAreaBtn_Click()
On Error GoTo TOEnterByAreaBtn_Click_Err

    Dim stDocName As String
    Dim stLinkCriteria As String
    
        If IsNull(Me.SelectArea) Then
        DoCmd.Beep
        MsgBox "You must select an Area first.", vbExclamation, "No Area selected!"
        End
    End If

    ClientName.SetFocus
    'stLinkCriteria = "Forms![EnterItemTransocean]![TOItem_Subform.form]![Area]=" & Me.[SelectArea] & " And [ReportNumber] = '" & Me.[ReportNo] & "'"
    stLinkCriteria = " Me.[SelectArea]=" & Forms![EnterItemTransocean]![TOItem_Subform].Form![Area] & "'"
    If Forms!Main!ClientName = "TRA" Then
    stDocName = "EnterItemTransocean"
    Else
    stDocName = "EnterItemDROPS"
End If
    Debug.Print stLinkCriteria
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    
TOEnterByAreaBtn_Click_Exit:
    Exit Sub

TOEnterByAreaBtn_Click_Err:
    MsgBox Error$
    Resume TOEnterByAreaBtn_Click_Exit
    
End Sub
 

Attachments

  • Screenshot_1.png
    Screenshot_1.png
    3.2 KB · Views: 74

Minty

AWF VIP
Local time
Today, 16:15
Joined
Jul 26, 2013
Messages
10,354
This code doesn't make sense ;
Code:
 stLinkCriteria = " Me.[SelectArea]=" & Forms![EnterItemTransocean]![TOItem_Subform].Form![Area] & "'"
[COLOR="Red"]                     You are referencing the form you are going to open here ^^^^^^
 - obviously that can't be correct.[/COLOR]
    If Forms!Main!ClientName = "TRA" Then
    stDocName = "EnterItemTransocean"
    Else
    stDocName = "EnterItemDROPS"
End If
    Debug.Print stLinkCriteria
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Your criteria needs to be something like

stLinkCriteria = "[The name of the field in the form you are about to open]='" & Me.[the name of the control on the form you are ON ] & "'"
 

Sam Summers

Registered User.
Local time
Today, 16:15
Joined
Sep 17, 2001
Messages
939
Good morning Minty and thank you

I am trying to understand the syntax here but struggling?

This is the code I have just tried now but I am getting the error message as attached?

Code:
Private Sub TOEnterByAreaBtn_Click()
On Error GoTo TOEnterByAreaBtn_Click_Err

    Dim stDocName As String
    Dim stLinkCriteria As String
    
        If IsNull(Me.SelectArea) Then
        DoCmd.Beep
        MsgBox "You must select an Area first.", vbExclamation, "No Area selected!"
        End
    End If

    ClientName.SetFocus
    stLinkCriteria = "[EnterItemTransocean]![TOItem_Subform].Form![Area]='" & Me.[SelectArea] & " And [ReportNumber]= '" & Me.[ReportNo] & " '"
    If Forms!Main!ClientName = "TRA" Then
    stDocName = "EnterItemTransocean"
    Else
    stDocName = "EnterItemDROPS"
End If
    Debug.Print stLinkCriteria
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    
TOEnterByAreaBtn_Click_Exit:
    Exit Sub

TOEnterByAreaBtn_Click_Err:
    MsgBox Error$
    Resume TOEnterByAreaBtn_Click_Exit
    
End Sub
 

Attachments

  • Screenshot_2.png
    Screenshot_2.png
    5 KB · Views: 64

Minty

AWF VIP
Local time
Today, 16:15
Joined
Jul 26, 2013
Messages
10,354
Don't use the full form syntax for the criteria expression, this
Code:
stLinkCriteria = "[EnterItemTransocean]![TOItem_Subform].Form![Area]='"
Should be this;
Code:
stLinkCriteria = "[Area]='"Me.[SelectArea] & "[COLOR="Red"]'[/COLOR] And [ReportNumber]= '" & Me.[ReportNo] & "' "
you only reference the Field - it knows what form it is on - you are opening it. Also note the missing quote I've added back around the string.

I think you are also going to struggle a bit with your thought process here. Reading between the lines you are trying to set a criteria on the Subform - you can't do that when the command is opening the EnterItemTransocean (Parent) form. Your Parent form should control what is displayed on the SubForm.
 

Sam Summers

Registered User.
Local time
Today, 16:15
Joined
Sep 17, 2001
Messages
939
OMG!

Now its coming back with a syntax error!?

I cant believe this is so hard?

All I am trying to do is to keep the area selected so that the user can keep entering new records without constantly having to select the area all the time?

I am also doing the same for 'Section' and then 'SubSection' and then for all three (Area, Section, SubSection).

If I cant achieve this then the whole DB is useless as it would mean that the user would have to select every single option for every single record when they have just inspected an area or a sub-section and just want to enter all the records for that area only.

Nightmare! :banghead:
 

Users who are viewing this thread

Top Bottom