Solved

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

Posted on 2015-01-03
23
255 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
  • 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 119

Expert Comment

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

If DCount("*", "TableName","Editswnumber= " & Me.EditSWNumber) = 0 Then
0
 
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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 119

Expert Comment

by:Rey Obrero
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 500 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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

760 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now