Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 296
  • Last Modified:

Access 2013 Check if combobox contains a record and return a message if Null

Hello Experts and thank you in advance

I have a form [createdayworkmenuform] with three combo boxes

Project No                                       [createprojectvo]
Variation No                                   [editvonumber]                                
Works Instruction Number          [editswnumber]

When you select the project No record  in combo box  [createprojectvo] the Variation No combo box [editvonumber] updates based on a query [Attachment Query 1] and the variations for that projects are the available records in combo box Variation No [editvonumber].

And the same happens when you select a record for the Variation No Combo box [editvonumber] the Works Instruction Number combo box  [editswnumber] updates based on a query [Attachment Query 2] and the work instructions are the available records in the combo box Works Instruction Number combo box  [editswnumber].

All works well but I want to introduce some user functionality by alerting the user that no records exists before they input each combo box.

If a project has no Variations then after you enter the project I want a message box to alert "No Variations have been created for this project"

And the same for work instructions when you select the variation no.  If no work instructions do not exist then a message box " No Work instructions have been created for this project"

Not sure if I need DCount or something like the code I have been trying without success

If DCount("*", "Editswnumber= " & Me.EditSWNumber) < -1 Then
MsgBox "No Work Instructions exist for this project !", vbInformation, "CMWorkflow Data Required"
Me.EditSWNumber.SetFocus
Me.NoRecords.Visible = True
Exit Sub
End If

Open in new window

 
The above code does not actually fire up the message and not sure why.

Thanks in advance

Anthony
Add-Daywork-Screen.JPG
Query-1.JPG
Query-2.JPG
0
Surveyor1
Asked:
Surveyor1
  • 12
  • 7
  • 2
  • +1
1 Solution
 
Phillip BurtonCommented:
Line 1 - how can DCount ever equal less than minus 1? Surely the smallest DCount can return is zero?
0
 
Surveyor1Author Commented:
Hi

I get a invalid use of Null using the DCount  if I use Null or 0 or ""

I am not an expert just a beginner on VB so that's why I am asking the question and not sure what your comment is advising me.

Is it possible what I am trying to achieve

Thanks in advance

Anthony
0
 
Rey Obrero (Capricorn1)Commented:
you need a name of domain (table or query)

If DCount("*", "TableName","Editswnumber= " & Me.EditSWNumber) = 0 Then
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Phillip BurtonCommented:
You are missing an argument in the DCount.

It is expression, domain, criteria.  You are missing the domain (I.e. The table or the query).

See http://www.techonthenet.com/access/functions/domain/dcount.php for more details.

And when you have updated that bit, it should =0 instead of <-1.
0
 
Surveyor1Author Commented:
Rey

I get the attached error message 3075 using the following code

Private Sub EditVONumber_Change()
If DCount("*", "siteworksummary", "swnumber= " & Me.EditSWNumber) = 0 Then
MsgBox "No Work Instructions exist for this project !", vbInformation, "CMWorkflow Data Required"
Me.EditSWNumber.SetFocus
'Me.NoRecords.Visible = True
Exit Sub
End If
End Sub

Open in new window


I am trying to reference to table [siteworksummary] and field [swnumber] as per the attached Query 2

Am I referencing the correct table and field ?

Not sure what to do with this

Thanks in advance

Anthony
Error-Message-3075.JPG
Query-2.JPG
0
 
mbizupCommented:
From your earlier comment, it sounds like you are specifically having problems with nulls/blanks.

Try this:

If DCount("*", "siteworksummary", "swnumber= " &  NZ(Me.EditSWNumber, -1) ) = 0  then

Open in new window


Note that the "-1" is an arbitrary value... one which you would NOT expect to see in the swNumber field, to be used as a substitute for nulls.

Also, the above assumes that swnumber is *numeric*.  If it is a text field, use this instead:

If DCount("*", "siteworksummary", "swnumber= " &  Chr(34) &  NZ(Me.EditSWNumber, "") & Chr(34) ) = 0  then

Open in new window

0
 
Surveyor1Author Commented:
Hi Experts

Does the DCount actually answer the original question ??

Or can an alternative method be used ??  as I am getting no joy with the example given.

I have tried all possible table and field names and the message does not appear

Thanks in Advance
0
 
Surveyor1Author Commented:
Mbizup

Again tried your code and nothing happens each combo box just lets you fill in data and if no data exists no message appears ?

Not sure if this is actually checking to see if any records exist ??

Do I put this in the after update of the pervious combo box ??

Anthony
0
 
mbizupCommented:
In terms of determining whether records exist for projects or variations, then yes - DCount should work.  You need to get the tables right though.

This contains prompts for you to fill in the actual table and field names:


If DCount("*", "TheTableThatContainsProjectsAndVariations", "ProjectFieldName= " &  NZ(Me.createprojectvo, -1) ) = 0  then

Open in new window


If Project is TEXT:

If DCount("*", "TheTableThatContainsProjectsAndVariations", "ProjectFieldName= " & chr(34) & NZ(Me.createprojectvo, -1)  & chr(34) ) = 0  then

Open in new window


The code would go in the After Update event of the Project combo box.


Similar code for variations/work instructions...
0
 
mbizupCommented:
<<Access 2013 Check if combobox contains a record and return a message if Null >>

A simpler solution, however to the *title* of your question is to just check the ListCount property of the next combo box.

For example, in the after update event of the Project combo  (or in the got focus event of the variation combo, which to my understanding is named 'editvonumber'):

If Me.editvonumber.Listcount = 0 then
     MsgBox "No variations for this project"
End If

Open in new window

0
 
mbizupCommented:
Just a note - when using the List count property, use 1 instead or 0 if your combo box includes row headers, because the headers are seen as a 'row'.
0
 
Surveyor1Author Commented:
Thank you

The project combo will always have records so I need only for Variations and Work Instructions

So for Variations I have used the following code (Some old ignore) and the new code

Private Sub CreateProjectVO_AfterUpdate()

'***Code to make stars next to fields***

If IsNull(Me.CreateProjectVO) Or Me.CreateProjectVO = "" Then
      Me.DWStar2.Visible = True
      Else
      Me.DWStar2.Visible = False
        Exit Sub
    End If
Me.Requery

'***New code entred here***

If DCount("*", "VOsummary", "VOnumber= " & Nz(Me.CreateProjectVO, -1)) = 0 Then
MsgBox "No Variation Records exist for this project", vbInformation, "CMWorkflow Data Required"
    Me.CreateProjectVO.SetFocus
Exit Sub
End If
End Sub

Open in new window


This is based on the attached file showing the query linked to variation combo box and showing the tables

Again nothing happens ?

Thanks

Anthony
Tables-and-query-for-VOnumber.JPG
0
 
Rey Obrero (Capricorn1)Commented:
the criteria in your query is not correct

the correct format is

[Forms]![VO Database Control Menu]![SFSubform1].FORM![CreateProjectVO]

use the above format for all your criteria
0
 
Surveyor1Author Commented:
Rey

Thank you

The query has worked well for a long time ?  not sure why I would want to change the query does this solve the DCount issue as I am not sure ??

Anthony
0
 
Surveyor1Author Commented:
Rey

Just changed the criteria on the query but still nothing the form just works as normal and no message box from the code

Not sure if we have moved away from my original question ??

All I am looking to do is use a working form and just have it alert the user if no subsequent record exist as the user progresses down the form

i.e Complete the Project Number  combo box 1 (Records will always exist) then before the user enters the next comb box 2 [Variation] a message box appears if no variation records are returned in combo box 2 from the query based on the project No in combo box 1and the same then for combo box 3 based on the query using criteria from combo box 1 and 2

Maybe I am looking for something that cannot be done in access ?

Thanks again in advance

Anthony
0
 
mbizupCommented:
Did you see my comment about testing the *ListCount* property of the combo box?

That will determine whether or not the combo has rows... regardless of the data or where it comes from.
0
 
Surveyor1Author Commented:
Hi mbizup

Yes thank you to change the 0 to 1 if headers ??  No headers so left at 0

Thanks

Anthony
0
 
Surveyor1Author Commented:
Apologies just seen your previous message and will try now
0
 
Surveyor1Author Commented:
Mbizup

Thank you your solution is working as follows

Private Sub EditSWNumber_GotFocus()
If Me.EditSWNumber.ListCount = 0 Then
MsgBox "No Work Instructions have been created for this project", vbExclamation, "CMWorkflow Data Required"
End If
End Sub

Open in new window


but only for gotfocus() and not for afterupdate()

Finally getting there :-)
No-Work-Instructions-Message.JPG
0
 
mbizupCommented:
Try either of the following... using the after update event.  These are two different methods of requerying the combo before checking the number of rows.

Me.EditSWNumber.rowsource = me.EditSWNumber.rowsource
If Me.EditSWNumber.ListCount = 0 Then
MsgBox "No Work Instructions have been created for this project", vbExclamation, "CMWorkflow Data Required"
End If

Open in new window


Me.EditSWNumber.requery
If Me.EditSWNumber.ListCount = 0 Then
MsgBox "No Work Instructions have been created for this project", vbExclamation, "CMWorkflow Data Required"
End If

Open in new window

0
 
Surveyor1Author Commented:
Mbizup

Great!!  used the first code and it works a treat and thank you for sticking with this to solve just what I wanted and makes the user interface a lot more appealing

I am enjoying learning access and VB and use it to solve problems for my team, again thank you

I have included the final code as follows and screenshot for future reference for others to use.  I added some labels so that they also advise work instruction is missing [NoWI. Visible = true or false] and the same for green stars to indicate user input into the combo box is required.

' Code to check if Work Instruction exsists for the chosen Variation

Private Sub EditVONumber_AfterUpdate()
Me.EditSWNumber.RowSource = Me.EditSWNumber.RowSource
If Me.EditSWNumber.ListCount = 0 Then
Me.NoWI.Visible = True
MsgBox "No Work Instructions have been created for this project", vbExclamation, "CMWorkflow Data Required"
Else
Me.NoWI.Visible = False
Exit Sub
End If
Me.EditSWNumber.Requery
If IsNull(Me.EditVONumber) Or Me.EditVONumber = "" Then
      Me.DWStar.Visible = True
      Else
      Me.DWStar.Visible = False
    End If
End Sub

Open in new window


I will allocate the points accordingly :-)

Anthony
No-Work-Instructions-Message-Final.JPG
0
 
mbizupCommented:
Glad to help out!
0
 
Surveyor1Author Commented:
Easy to follow advice and stayed with finding the best solution over a number of iterations and numerous comments and questions.  Thank you.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 12
  • 7
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now