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
> Dcount With Dmax, Access 2016    
 
   
SallyCh
post Oct 4 2019, 07:06 AM
Post#1



Posts: 81
Joined: 24-October 14
From: UK


Hi all

I'm not sure if I've picked the correct Forum section, but I'm struggling with a DCount, using Dmax in the criteria.

I need to check to see if TblInput has a single record with both the last receivedID, together with the ID of the person inputting the records.

I've broken the parts down and they work separately:

DMax("receivedid","tblreceived") Result = 3148

[Forms]![login]![cboname] Result = 29

DCount("*","tblinput","[receivedfk]=3148") Result = 1

However, this doesn't work:

DCount("*","tblinput","[receivedfk]= DMax("receivedid","tblreceived")")

although this works

DCount("*","tblinput","[inputfk]=[Forms]![login]![cboname]")


I've put the following on a test button, but I'm getting a run time error code 13:

idate as integer
ilogged as integer

idate = DMax("receivedid", "tblreceived")
ilogged = [Forms]![login]![cboname]

if DCount("*","tblinput","[receivedfk]= idate" And "[inputfk] = ilogged") =0 then
msgbox "no record - append record"
else
msgbox "record - do nothing"
end if

Some assistance with the above would be greatly appreciated, or, is there a better way to get the same result.
Go to the top of the page
 
arnelgp
post Oct 4 2019, 07:11 AM
Post#2



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...



DCount("*","tblinput","[receivedfk]= " & DMax("receivedid","tblreceived"))

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
nvogel
post Oct 4 2019, 07:16 AM
Post#3



Posts: 1,023
Joined: 26-January 14
From: London, UK


You can try something like the following (untested). Anything you can do with DCOUNT and DMAX can also be done with queries.

SELECT COUNT(*) AS cnt
FROM tblInput
WHERE receivedfk = (SELECT MAX(receivedid) FROM tblreceived);


This post has been edited by nvogel: Oct 4 2019, 07:17 AM
Go to the top of the page
 
orange999
post Oct 4 2019, 07:21 AM
Post#4



Posts: 1,986
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


SallyCh,

What exactly is the rowsource of your combobox? You will need to add criteria identifying the 'person entering the data' to the solutions offered above.

"I need to check to see if TblInput has a single record with both the last receivedID, together with the ID of the person inputting the records."

--------------------
Good luck with your project!
Go to the top of the page
 
SallyCh
post Oct 11 2019, 05:54 AM
Post#5



Posts: 81
Joined: 24-October 14
From: UK


Thanks Arnelgp, that sorted that little issue out, but I'm still getting runtime 13 when I have multi criteria on the Dcount.

I hope my following explanation does not get confusing. I have a habit of over explaining myself dazed.gif

The TblInput has 3 fields, InputID, ReceivedFK & InputFK (I now realise the labels would be less confusing if they were DateFK & UserFK). The InputID is then used in the main TblData table, to indentify the date and user.

There should only be one record per date AND user.

[Forms]![login]![cboname] is Unbound and uses the UserID from the Tbl_person. Once you've logged in, the form is always open, but hidden, so you can identify who is entering the records (this works fine for all other uses within the database)

When adding a record to the main TblData, I need to check if the User has already added some records that day. If so, use the InputID already created, if not, then create a new record in TblInput and use that InputID.

This is what is causing the Runtime 13 error.

DCount("*", "tblinput", "[receivedfk]= " & DMax("receivedid", "tblreceived") And "[InputFK] = [Forms]![login]![cboname]")

It's the 2nd criteria that is causing the problem, but I know the InputFK and [Forms]![login]![cboname] are both numbers.

Thanks again for your comments.
Go to the top of the page
 
RJD
post Oct 11 2019, 06:19 AM
Post#6


UtterAccess VIP
Posts: 10,131
Joined: 25-October 10
From: Gulf South USA


Hi Sally: Looks like a bit of a syntax issue. Try this...

DCount("*", "tblinput", "[receivedfk]= " & DMax('receivedid', 'tblreceived') & " And [InputFK] = " & [Forms]![login]![cboname])

This is untested but should be pretty close.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
SallyCh
post Oct 11 2019, 06:35 AM
Post#7



Posts: 81
Joined: 24-October 14
From: UK


Thanks RJD, that did the trick.

I'm self taught and always find the Syntax an issue.

Many thanks again.
Go to the top of the page
 
RJD
post Oct 11 2019, 06:43 AM
Post#8


UtterAccess VIP
Posts: 10,131
Joined: 25-October 10
From: Gulf South USA


You are very welcome. Glad that worked for you.

Yes, the syntax takes some getting-used-to. It's a lot about what's inside the quotes vs what's outside. And we are always here to assist if you need it ...

Continued success with your project ...

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
SallyCh
post Oct 11 2019, 07:21 AM
Post#9



Posts: 81
Joined: 24-October 14
From: UK


Now, that's sorted, I seem to have another issue.

This is what I have:

Private Sub Form_Current()
Dim Dlog As Date
Dim ilogged As Integer
Dim breceived As Boolean
Dim idate As Integer
Dim iinput As Integer
Dim idateinput As Integer
Dim slogged As String
Dim iinputID As Integer
Dim idcount As Integer


Dlog = DMax("date_received", "tblreceived") 'last date received
idate = DMax("receivedid", "tblreceived") 'last datereceived id
ilogged = [Forms]![login]![cboname] 'person id
idateinput = DMax("receivedfk", "tblinput") ' last date received in input
breceived = DMax("receivedfk", "tblinput") = DMax("receivedid", "tblreceived")
iinput = DMax("inputid", "tblinput")
slogged = [Forms]![login]![cboname].Column(4) 'Person full name
idcount = DCount("*", "tblinput", "[receivedfk]= " & DMax("receivedid", "tblreceived") & " And [InputFK] = " & [Forms]![login]![cboname])


If Me.NewRecord Then

'__________________
If Dlog < Date Then

DoCmd.RunSQL "INSERT INTO TblReceived ( Date_received ) " & _
" SELECT Date() AS Expr1;"
End If
'__________________

If idcount > 0 Then
MsgBox "record found"
Else
MsgBox "No record"

DoCmd.RunSQL "INSERT INTO TblInput ( receivedFK, InputFK ) " & _
" SELECT Max(TblReceived.ReceivedID) AS MaxOfReceivedID, [Forms]![login]![cboname] AS Expr1 " & _
" FROM TblReceived " & _
" GROUP BY [Forms]![login]![cboname];"
Me.requery

End If

Me.txtinputfk = iinput
Me.txtreceived = Dlog
Me.txtLoggedIn = slogged
End If

End Sub

All works, and a new record is added to TblInput, if needed. However, iInput doesn't update, and gives the previous InputID

Please feel free to point out any poor coding, as I'm always open to learning and improving.



Go to the top of the page
 
arnelgp
post Oct 11 2019, 08:09 AM
Post#10



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


CODE


With Currentdb.CreateQuerydef("", "INSERT INTO TblInput (receivedFK, InputFK ) SELECT p0, p1;")
    .Parameters("p0") = DMax("ReceivedID", "TblReceived")
    .Parameters("p1") = [Forms]![login]![cboname]
    .Execute
End With
Me.Requery

End If

Me.txtinputfk = DMax("inputid", "tblinput")
Me.txtreceived = Dlog
Me.txtLoggedIn = slogged
End If

End Sub

This post has been edited by arnelgp: Oct 11 2019, 08:13 AM

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
SallyCh
post Oct 14 2019, 04:24 AM
Post#11



Posts: 81
Joined: 24-October 14
From: UK


Thanks again Arnelpg,

As usual, the UtterAccess community has helped with an issue I've spent days trying to fix on my own. notworthy.gif

Go to the top of the page
 
arnelgp
post Oct 14 2019, 04:37 AM
Post#12



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


you're welcome and goodluck!

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th November 2019 - 06:37 PM