Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2015-01-03
23
Medium Priority
?
295 Views
Last Modified: 2015-01-04
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
Comment
Question by:Surveyor1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 7
  • 2
  • +1
23 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40529499
Line 1 - how can DCount ever equal less than minus 1? Surely the smallest DCount can return is zero?
0
 

Author Comment

by:Surveyor1
ID: 40529509
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40529531
you need a name of domain (table or query)

If DCount("*", "TableName","Editswnumber= " & Me.EditSWNumber) = 0 Then
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40529536
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
 

Author Comment

by:Surveyor1
ID: 40529591
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
 
LVL 61

Expert Comment

by:mbizup
ID: 40529620
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
 

Author Comment

by:Surveyor1
ID: 40529622
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
 

Author Comment

by:Surveyor1
ID: 40529635
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
 
LVL 61

Expert Comment

by:mbizup
ID: 40529639
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
 
LVL 61

Expert Comment

by:mbizup
ID: 40529653
<<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
 
LVL 61

Expert Comment

by:mbizup
ID: 40529657
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
 

Author Comment

by:Surveyor1
ID: 40529661
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40529665
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
 

Author Comment

by:Surveyor1
ID: 40529678
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
 

Author Comment

by:Surveyor1
ID: 40529692
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
 
LVL 61

Expert Comment

by:mbizup
ID: 40529693
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
 

Author Comment

by:Surveyor1
ID: 40529696
Hi mbizup

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

Thanks

Anthony
0
 

Author Comment

by:Surveyor1
ID: 40529697
Apologies just seen your previous message and will try now
0
 

Author Comment

by:Surveyor1
ID: 40529767
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
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 40529794
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
 

Author Comment

by:Surveyor1
ID: 40530012
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
 
LVL 61

Expert Comment

by:mbizup
ID: 40530029
Glad to help out!
0
 

Author Closing Comment

by:Surveyor1
ID: 40530066
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question