UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Vba To Find Empty Field, Access 2016    
 
   
JonathanT
post Dec 12 2019, 08:52 AM
Post#1



Posts: 17
Joined: 30-October 11



I'm trying to the VBA code to find the first empty value in a specific field of a recordset. This will run when a user clicks a button on a form. The record with the blank field will display on the form. Any help would be appreciated. I'm trying to avoid clicking on the Find option in the ribbon and typing in what I want find every time. I'd rather just click a button since this is a frequently repeated action
Go to the top of the page
 
Larry Larsen
post Dec 12 2019, 09:19 AM
Post#2


UA Editor + Utterly Certified
Posts: 24,410
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi
Please tell us more about the record and which field within record your looking for..

Does seem an odd process that the user is asked to do..??

Other details that would help us, details about the table and relationships if any..

thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
projecttoday
post Dec 12 2019, 09:23 AM
Post#3


UtterAccess VIP
Posts: 11,439
Joined: 10-February 04
From: South Charleston, WV


You need to define what you mean by "first".

The are several functions that search for records: Dmin, Dmax, Dfirst, Dlast. So what you can do is in the code behind the button find the record you want with one of these functions and then open the form (Docmd.OpenForm) with the WHERE condition specifiying that record.

--------------------
Robert Crouser
Go to the top of the page
 
GroverParkGeorge
post Dec 12 2019, 09:32 AM
Post#4


UA Admin
Posts: 36,496
Joined: 20-June 02
From: Newcastle, WA


Sometimes a statement of the reason for a process, or the goal it is supposed to achieve, can help us understand what needs to be done better than the mechanics desired.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
cheekybuddha
post Dec 12 2019, 09:36 AM
Post#5


UtterAccess Moderator
Posts: 12,064
Joined: 6-December 03
From: Telegraph Hill


More detail would probably be a good thing, but from your description you ought to be able to do something like:
CODE
  With Me.RecordsetClone
    .MoveFirst
    .FindFirst "Len(SpecificFieldName & vbNullString)  = 0"
    If Not .NoMatch Then
      Set Me.Bookmark = .Bookmark
    Else
      MsgBox "No blanks found in SpecificFieldName"
    End If
  End With


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
JonathanT
post Dec 12 2019, 01:57 PM
Post#6



Posts: 17
Joined: 30-October 11



I've attached a screen shot of sample data. I want to find record 1709 where the FirstIssue field is empty


Attached File(s)
Attached File  Survey.PNG ( 57.67K )Number of downloads: 17
 
Go to the top of the page
 
tina t
post Dec 12 2019, 03:27 PM
Post#7



Posts: 6,281
Joined: 11-November 10
From: SoCal, USA


CODE
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
rst.FindFirst "FirstIssue Is Null"
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
End If
rst.Close
Set rst = Nothing

the above assumes that the command button will be on the same form as the records you're searching. if you have a mainform/subform setup, with the button on the mainform and the records on the subform, you'll have to change the form reference to point to the subform instead.

hth
tina
This post has been edited by tina t: Dec 12 2019, 03:28 PM

--------------------
"the wheel never stops turning"
Go to the top of the page
 
kfield7
post Dec 12 2019, 03:33 PM
Post#8



Posts: 1,031
Joined: 12-November 03
From: Iowa Lot


Another approach would be to filter the form's recordset to all records where the field is null or =""
Go to the top of the page
 
isladogs
post Dec 12 2019, 04:08 PM
Post#9


UtterAccess VIP
Posts: 2,088
Joined: 4-June 18
From: Somerset, UK


Or run a query
CODE
SELECT TOP 1 * FROM YourTableName WHERE FIrstIssue Is Null ORDER BY CommentID;

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
Larry Larsen
post Dec 12 2019, 04:12 PM
Post#10


UA Editor + Utterly Certified
Posts: 24,410
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi
May I also post up a very small demo using North-winds data..

Looking for an "empty" address field..

Opps I only have Acc2010 on LT..

HTH's
thumbup.gif
Attached File(s)
Attached File  Demo_UA_12122019.zip ( 26.98K )Number of downloads: 7
 

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
projecttoday
post Dec 12 2019, 05:28 PM
Post#11


UtterAccess VIP
Posts: 11,439
Joined: 10-February 04
From: South Charleston, WV


Another.
Attached File(s)
Attached File  FindEmptydb.zip ( 29.22K )Number of downloads: 10
 

--------------------
Robert Crouser
Go to the top of the page
 
JonathanT
post Dec 13 2019, 10:49 PM
Post#12



Posts: 17
Joined: 30-October 11



Thanks to everyone. I went with the suggestion made by projecttoday
Go to the top of the page
 
projecttoday
post Dec 13 2019, 10:54 PM
Post#13


UtterAccess VIP
Posts: 11,439
Joined: 10-February 04
From: South Charleston, WV


yw.gif

--------------------
Robert Crouser
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th January 2020 - 02:31 PM