Solved Accessing Field Values of SubForm of a Navigation Form for DMax Function

[Pac-Man]

Hello,

I'm using DMax function on my form frmReportAdd to reset ReportPartialNo for every new project in tblReports. Function I used is:

[ReportPartialNo] = Nz(DMax("[ReportPartialNo]", "tblReports", "[ProjectCode]=[Forms]![frmReportAdd]![ProjectCode]"), 0) + 1

Now I created a Navigation Form frmRMS and on the first tab (named as HomeButton) of navigation form, I used form frmReportAdd on which I'm using aforementioned DMax function. Now after navigation form, Dmax is not working. I don't work. Every time it gives the same number (i.e. 01). I searched this forum and found this link and tried the code but neither worked.

[ReportPartialNo] = Nz(DMax("[ReportPartialNo]", "tblReports", "[ProjectCode]=[Forms]![frmRMS].[Form].[Controls]![frmReportAdd].[Form].[ProjectCode]"), 0) + 1

And

[ReportPartialNo] = Nz(DMax("[ReportPartialNo]", "tblReports", "[ProjectCode]=[Forms]![frmRMS]![HomeButton].[Controls].[Form]![frmReportAdd].[Controls].[ProjectCode]"), 0) + 1

I also tried to make code by using instructions on Access MVPS this page but that didn't work either. Now the code don't run and gives error. Please help me fix this, I'm stuck here.

PS. I'm using Microsoft Access 2016.

Best Regards,
Abdullah

 

[Deleted member 147267]

[ReportPartialNo] = Nz(DMax("[ReportPartialNo]", "tblReports", "[ProjectCode]=[Forms]![frmReportAdd]![ProjectCode]"), 0) + 1

Did that ever work? I would think not because in all cases that you show it appears that you have not concatenated the variable correctly in the DMax criteria. You don't want to refer to something named "[Forms]![frmReportAdd]![ProjectCode]" you want to use its value. Try
[ReportPartialNo] = Nz(DMax("[ReportPartialNo]", "tblReports", "[ProjectCode]= " & [Forms]![frmReportAdd]![ProjectCode]), 0) + 1 I'm assuming 2 things here: that projectcode is a number data type, and in your last example, you have the syntax correct for when using a nav form. If not, we need to know the name of the navigation form, the name of the navigation subform (it is a control), and the name of the form that the control is on. If you did not change the default names when creating the navigation form, the form name is likely Navigation Form and the control name would be NavigationSubform. Don't confuse navigation control (contains the buttons) with the navigation subform control.

 

[strive4peace]

Abdullah, the problem with the Navigation control is that is whatever isn't active isn't loaded to use. When I used to use them (I've switched back to using the Tab control), I saved information in a database property or TempVar

to reference mycontrolname in a navigation subform, here is the syntax:

Forms!MyFormname!NavigationSubform.form!mycontrolname

NavigationSubform is the name that Microsoft uses for the collection of forms on different tabs of the navigation control. You can change this name if you want.

 

[strive4peace]

to assign or change a TempVar:

TempVars!myTempVarName = myValue

there is no declaring a data type

then to reference the TempVar, which you can do in a query too, just use TempVars!myTempVarName

 

[strive4peace]

ps, also pay attention to what Micron said about taking the reference out of the criteria string ...

 

[Pac-Man]

Did that ever work?

First of all, thanks for reply. Yes, it was working fine till I used frmReportAdd. It didn't work after i linked frmReportAdd with navigation form frmRMS. After putting in Navigation form, i think command cannot get criteria for to incorrect code for calling ProjectCode from the form.

I'm assuming 2 things here: that projectcode is a number data type,

ProjectCode is there character alphanumeric code.

we need to know the name of the navigation form, the name of the navigation subform (it is a control), and the name of the form that the control is on.

Names are as follows:
Navigation form: frmRMS
Tab Name: HomeButton (I've changed from default)
Form Name which is used in the HomeButton Tab (navigation sub for.): frmReportAdd
Control Name for DMax Criteria: ProjectCode

 

[Pac-Man]

Navigation control is that is whatever isn't active isn't loaded to use. When I used to use them (I've switched back to using the Tab control), I saved information in a database property

Form is active and it saves values in the related table tblReports. Problem is, it generate incorrect ReportPartialNo because function DMax cannot get ProjectCode value for criteria. That is why it generate Everytime 1 value (0+1).

 

[Pac-Man]

Forms!MyFormname!NavigationSubform.form!mycontrolname

Sorry for asking this thing because I'm new to VBA, do I've to write my commands like following for my case:

 ProjectCode!frmRMS! frmReportAdd.ProjectCode

Or like following:

ProjectCode!frmRMS! NavigationSubform.ProjectCode

 

[Deleted member 147267]

Every time I help out with this, even after 2 or 3 attempts I still don't know the names that are required. This time is no different - you didn't provide what was asked for. That's understandable if you just don't know what these things are called and that's OK. So copy your db, run a compact/repair on that, zip it then post it here, otherwise we'll be dancing around this for 20 or so posts. strive4peace is correct about the way a nav form works but I took it that the control to be edited is on the same form that is visible when you click the nav button because you wrote

I used form frmReportAdd on which I'm using aforementioned DMax function.

"[ProjectCode]= " & [Forms]![frmReportAdd]![ProjectCode]),

If you have information in the db that is private, just remove everything from the db copy that is not required for this.

 

[strive4peace]

Abdullah, you can use the Builder to help you create a proper reference.

1. Open your Navigation Form to the page you want criteria from

2. in your query design, in the desired criteria cell, press Ctrl-F2 for the Expression Builder

3. on the left, in the Expression Elements section, click + to the left to expand the choices for your database. My database name is NavigationControl.accdb.

4. click the "+" button to the left to expand the choices for objects in the database

5. click + to expand choices for Forms

6. click + to expand choices for Loaded Forms

I have a form called "Navigation Form". Normally, I don't use spaces in names, but in this case, I left the name as the default that Microsoft called it.

7. click + to expand choices for subforms on the loaded form. Since only one is loaded, it is displayed.
Pick the name of the form. In my case, it is "f_CONTACTS
In the center section, Expression Categories, double-click the control name you want
I chose CID

Access then puts the resulting expression in the top pane.
In my case, this is:
Forms![Navigation Form]![NavigationSubform].Form![CID]

WHERE:
Navigation Form is the name of the form in the Navigation Pane
NavigationSubform is the name of the control holding the collection of forms
CID is the control name for the value in my criteria

 

[Pac-Man]

copy your db, run a compact/repair on that, zip it then post it here,

Here is the database. You can see, button runs fine on frmReportAdd and generates valid number (i.e I'm restarting Report number when either project is changed or the customer is changed. But it don't work on frmRMS i.e. navigation form.

 

[Pac-Man]

you can use the Builder to help you create a proper reference.

Thank you so much for such a detailed reply. It worked like charm. My problem is solved. Thanks again for both of you @Micron and @strive4peace for your time and help.

 

[Pac-Man]

Can you please look into my database and see if I set the code correctly to reset ReportPartialNo every time either new project or new customer is added.

 

[strive4peace]

hi Abdullah,

Since you don't always use the form on the navigation form, I set the code up to use an object variable for the form that changes if the navigation form is being used. NOTE: I hard-coded the name of the navigation form in there, so if you change it, or change the name of the navigation control, you'll also have to change the code.

Private Sub GenerateCommand_Click()
'190419 modified by strive4peace

   'dimension variables
   Dim sProjectCode As String _
      , sCustomerCode As String _
      , sReportNo As String _
      , nReportPartialNo As Long _
      , bGetPartial As Boolean
      
   'dimension object variable for form
   Dim frm As Form
   
   'if using a navigation form, set form reference to its active subform
   If CurrentProject.AllForms("frmRMS").IsLoaded Then
      Set frm = Forms!frmRMS!NavigationSubform.Form
   Else
      'if form is opened on its own, form reference is Me
      Set frm = Me
   End If
      
   'use the ReportPartialNo control
   With frm.ReportPartialNo
      'if there is already a value, don't change it
      If Not IsNull(.Value) Then
         'give user a message
         MsgBox "ReportPartialNo already has a value", , "Nothing to do"
         'exit procedure
         GoTo Proc_Exit
      End If
   End With
   
   'use the ProjectCode control
   With frm.ProjectCode
      'make sure data is filled
      If IsNull(.Value) Then
         'give user a message
         MsgBox "You must enter a Project Code", , "Missing information"
         'exit procedure
         GoTo Proc_Exit
      End If
      sProjectCode = .Value
   End With
   
   'use the CustomerCode control
   With frm.CustomerCode
      'make sure data is filled
      If IsNull(.Value) Then
         'give user a message
         MsgBox "You must enter a Customer Code", , "Missing information"  'change message to whatever you want
         'exit procedure
         GoTo Proc_Exit
      End If
      sCustomerCode = .Value
   End With
   
   'calculate ReportPartialNo
   nReportPartialNo = Nz(DMax("[ReportPartialNo]", "tblReports" _
            , "[ProjectCode]=""" & sProjectCode & """" _
            & " And [CustomerCode]=""" & sCustomerCode & """"), 0) + 1
   'assign ReportPartialNo
   frm.ReportPartialNo = nReportPartialNo
   
   'calculate ReportNo
   sReportNo = sCustomerCode + "/" + sProjectCode _
      & "-" + CStr(Format(nReportPartialNo, "00#"))
   'assign ReportNo
   frm.ReportNo = sReportNo
   
Proc_Exit:
   'release object variable
   Set frm = Nothing
End Sub

I noticed that in your table, the SIZE of your text fields is allowed to be 255 characters. It would be a good idea to make this value smaller since they will never be that big.

It would also be a good idea to change the NAME property of your controls to match what they contain.

I like using variables, calculating them, and then assigning values to the controls using the variables.

Because some of the values are strings, they need to be delimited with single or double quotes. If you want a double quote mark in a string that is already delimited with double quote marks, you need 2 of them.

At the end, object variables have to be released (Set frm = Nothing)

Hopefully the comments will help you understand what is happening

 

[strive4peace]

Thank you so much for such a detailed reply. It worked like charm. My problem is solved. Thanks again for both of you @Micron and @strive4peace for your time and help.

Great! Glad the Expression Builder worked for you ~ you're welcome, happy to help

 

[Pac-Man]

hi Abdullah,

Since you don't always use the form on the navigation form, I set the code up to use an object variable for the form that changes if the navigation form is being used. NOTE: I hard-coded the name of the navigation form in there, so if you change it, or change the name of the navigation control, you'll also have to change the code.

Private Sub GenerateCommand_Click()
'190419 modified by strive4peace

   'dimension variables
   Dim sProjectCode As String _
      , sCustomerCode As String _
      , sReportNo As String _
      , nReportPartialNo As Long _
      , bGetPartial As Boolean
    
   'dimension object variable for form
   Dim frm As Form
 
   'if using a navigation form, set form reference to its active subform
   If CurrentProject.AllForms("frmRMS").IsLoaded Then
      Set frm = Forms!frmRMS!NavigationSubform.Form
   Else
      'if form is opened on its own, form reference is Me
      Set frm = Me
   End If
    
   'use the ReportPartialNo control
   With frm.ReportPartialNo
      'if there is already a value, don't change it
      If Not IsNull(.Value) Then
         'give user a message
         MsgBox "ReportPartialNo already has a value", , "Nothing to do"
         'exit procedure
         GoTo Proc_Exit
      End If
   End With
 
   'use the ProjectCode control
   With frm.ProjectCode
      'make sure data is filled
      If IsNull(.Value) Then
         'give user a message
         MsgBox "You must enter a Project Code", , "Missing information"
         'exit procedure
         GoTo Proc_Exit
      End If
      sProjectCode = .Value
   End With
 
   'use the CustomerCode control
   With frm.CustomerCode
      'make sure data is filled
      If IsNull(.Value) Then
         'give user a message
         MsgBox "You must enter a Customer Code", , "Missing information"  'change message to whatever you want
         'exit procedure
         GoTo Proc_Exit
      End If
      sCustomerCode = .Value
   End With
 
   'calculate ReportPartialNo
   nReportPartialNo = Nz(DMax("[ReportPartialNo]", "tblReports" _
            , "[ProjectCode]=""" & sProjectCode & """" _
            & " And [CustomerCode]=""" & sCustomerCode & """"), 0) + 1
   'assign ReportPartialNo
   frm.ReportPartialNo = nReportPartialNo
 
   'calculate ReportNo
   sReportNo = sCustomerCode + "/" + sProjectCode _
      & "-" + CStr(Format(nReportPartialNo, "00#"))
   'assign ReportNo
   frm.ReportNo = sReportNo
 
Proc_Exit:
   'release object variable
   Set frm = Nothing
End Sub

Because some of the values are strings, they need to be delimited with single or double quotes. If you want a double quote mark in a string that is already delimited with double quote marks, you need 2 of them.

At the end, object variables have to be released (Set frm = Nothing)

Hopefully the comments will help you understand what is happening

That is awesome. I was thinking to use another duplicate form of frmReportAdd if it was to be used other than navigation form. Thank you so much.

I noticed that in your table, the SIZE of your text fields is allowed to be 255 characters. It would be a good idea to make this value smaller since they will never be that big.

It would also be a good idea to change the NAME property of your controls to match what they contain.

Thanks for sparing time to review my database. I'll update length and names of fields accordingly.

 

[strive4peace]

you're welcome, Abdullah ~ happy to help