[X]My Assistant

[SallyCh]

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.

[arnelgp]

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

[nvogel]

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

[orange999]

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."

[SallyCh]

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

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.

[RJD]

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

[SallyCh]

Thanks RJD, that did the trick.

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

Many thanks again.

[RJD]

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

[SallyCh]

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.

[arnelgp]

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

[SallyCh]

Thanks again Arnelpg,

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

[arnelgp]

you're welcome and goodluck!