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
> Struggling With Dcount, Access 2016    
post Feb 13 2020, 07:11 PM

Posts: 2
Joined: 13-February 20

I am a newby to posting. I usually feel that I learn much more by reading the forum(s) and experimenting than having others solve my problems. Also, I have always been one to be quite and be thought a fool then to speak up and remove all doubt. However, i am perplexed on this. I have a table (tblUSDANumbers) that I wish to add new records (livestock tag records) too. I was trying to use the following code to make sure the record did not exist already and if not add the sequential tag numbers ([USDANumbers]). I believe I am having a problem with DCount but I cannot figure it out using various delimiters. I get a syntax error when the code is run. Any help is appreciated. Thanks in advance. [USDANumbers] and [prefix] are table fields

Private Sub cmdAdd_Click()

Dim tblUSDANumbers As DAO.Recordset
Dim x As Double
Dim y As Double
Dim a As Double
Dim b As String
Dim c As Integer

Set tblUSDANumbers = CurrentDb.OpenRecordset("SELECT * FROM [tblUSDANumbers]")
x = Me.txtBegNo.Value
y = Me.txtEndNo.Value
b = Me.txtPrefix.Value

For a = x To y
c = DCount("[USDANumber]", "tblUSDANumbers", "[USDANumber] = " & a & " And [prefix] = " & b)
If c > 0 Then
MsgBox "This number is already in the Data base." & vbCrLf & _
"No records will be added."
Exit Sub
tblUSDANumbers![prefix] = Me.txtPrefix.Value
tblUSDANumbers![USDANumber] = a
c = c + 1
End If
Next a
MsgBox (c & " new number records created.")
Set tblUSDANumbers = Nothing
End Sub
Go to the top of the page
post Feb 13 2020, 07:44 PM

Posts: 2
Joined: 13-February 20

Figured the proper syntax for DCount.

c = DCount("[USDANumber]", "tblUSDANumbers", "[USDANumber] = " & a & " And [prefix] = " & "'" & b & "'")

Thanks for your time.

Go to the top of the page
post Feb 13 2020, 10:34 PM

UA Moderator
Posts: 77,736
Joined: 19-June 07
From: SunnySandyEggo

Hi Tim. Welcome to UtterAccess! welcome2UA.gif

Congratulations on figuring it out on your own! Cheers! cheers.gif

Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    9th April 2020 - 05:17 AM