Should I be using Dlookup

[parishpete]

I have a small lending library database (100members, 70 books) and I'm trying (hard) to put a routine in at the point of lending entry to double check that the book isn't already "out on loan" in the database.

The loan table has 400+ entries with 5 fields containing 3 integers and 2 dates; loan ID, Member no., Title no., Month out and Month back. Loans are made on one day each month so there there no real date issues. The month back field is empty until the book is returned.

At the point of data entry, on my form (via list box) I need to check the title no. against a query on the loan table which returns a list of about 20 titles on loan where the month back field is empty.

It should be simple, if the title no. isn't on that list the entry is good, if it is on the list, there is a mistake somewhere.

I thought I probably needed some vba code that uses Dlookup in a function which I can call after update in my dropdown list but just prior to saving the record.

I've had a number of attempts at writing the code but failed.

I'm assuming that something like the following is needed

Dlookup ("[ID]","DVDs_on_loan", _
"[Loan_Title_no] = Form![Newtitleloan]")

For simplicity [Newtitleloan] is a form variable that holds the book no.

In theory it will return the ID of an earlier loan if the current title no. ( as yet usaved) appears in my query. However I a get a compile error "Expected: =

I just can't see where I'm going wrong.

Any help very welcome

Pete

[RuralGuy]

See if this link helps you with the syntax: http://access.mvps.org/access/general/gen0018.htm

[parishpete]

Hi RG

I've just tried the following but still get "expected:= on completion of typing the statement:

Private Function Check_Loan()

Dlookup ("[ID]","DVDs_on_loan", "[Loan_Title_no] = " & Forms!DVDs_Out!Newtitleloan)

End Function



The function is being called from within the form DVDs_Out.



I've also tried:

Dlookup ("[ID]","DVDs_on_loan", "[Loan_Title_no] = [Newtitleloan]")

and

Dlookup ("[ID]","DVDs_on_loan", "[Loan_Title_no] = Form![Newtitleloan]")

all give the same "=" error

ID is an integer, as is Loan_Title_no. The nub of the routine is that it should not return an ID no.

The function is on the form's vba page

It's got me stumped

regards

Pete

[RuralGuy]

This syntax is correct:
Dlookup ("[ID]","DVDs_on_loan", "[Loan_Title_no] = " & Forms!DVDs_Out!Newtitleloan)
...*but* what do you want to do with the results? That is what Access is trying to say. Something like:
YourVariable = Dlookup ("[ID]","DVDs_on_loan", "[Loan_Title_no] = " & Forms!DVDs_Out!Newtitleloan)

[parishpete]

This syntax is correct:
Dlookup ("[ID]","DVDs_on_loan", "[Loan_Title_no] = " & Forms!DVDs_Out!Newtitleloan)
...*but* what do you want to do with the results? That is what Access is trying to say. Something like:
YourVariable = Dlookup ("[ID]","DVDs_on_loan", "[Loan_Title_no] = " & Forms!DVDs_Out!Newtitleloan)

Thanks RG, I've got it cracked now, realised my error!

regards

Pete

[RuralGuy]

Excellent! Thanks for posting back with your success.

[parishpete]

Excellent! Thanks for posting back with your success.

RG

I thought it no bad thing to post the working code below. It's probably long-winded and lacks polish and as yet has no error handling, but it works!

Regards

Pete

Private Function Check_Loan() 'Checks DVD number against query list containing records of Books/DVDs that havn't been returned

[Newtitleloan] = 0 'Clears previous value of loan record ID

[Newtitleloan] = DLookup("[ID]", "DVDs_on_loan", "[DVD_No]=[Lastloan]") ' Checks to see if DVD is on the list (zero value = DVD isn't already on loan)


If [Newtitleloan] > 0 Then ' Greater than zero the number is on the list, this routine undoes the new record, finds the member's neme and the date lent, and displays them in a message

[Borrowed] = DLookup("[LastName]", "DVDs_on_loan", "[ID]=[Newtitleloan]")

[Whenborrowed] = DLookup("[Month out]", "DVDs_on_loan", "[ID]=[Newtitleloan]")

MsgBox "DVD already on loan to " & [Borrowed] & " " & [Whenborrowed]
DoCmd.RunCommand acCmdUndo


Else ' If the DVD isn't on the list the record is saved and displayed on the form as the last record entered using two unbound text boxes (as a reminder)
MsgBox "DVD loan OK"
DoCmd.RunMacro "Date_Out"
DoCmd.RunCommand acCmdSaveRecord
Me.[Text85] = [Combo30].Column(1)
Me.[Text89] = [Combo32].Column(1)

DoCmd.RunCommand acCmdRecordsGoToNew

End If

End Function