Noob needs help with DLookup function in form (1 Viewer)

[oZone]

I've made a home movie library database and for the most part it's complete but I thought of a handy feature to add last minute but I cant get it to work rite and need some help with it.

The main form lists movie details (title, genre, rating, description etc...) depending on which movie the user chooses from a combo box. I also have included borrowers tables and made a few forms that keep track of people who borrow movies.

What I want to do (and the part that isnt working) is put a text box on the main form that lists who (if anyone) is currently borrowing the currently selected movie.

The main form gets its data from a query, and I tried including the FriendID and Name from the Borrowers table in this query, then made a text box whos source was Name, but it didnt list the borrowers name.

I then tried to use a DLookup function for the text box, but I'm vaguely familiar with it and dont know how to get it working. I got it as far as displaying the FriendID, but for some reason cant get it to display the borrowers Name. Here's the DLookup that displayed the FriendID:

=DLookUp("[FriendID]","tblLoanList","[MovieID]=Forms![frmAddEditMovies]![MovieID]")

Since I couldnt get that to work I tried a different method that also doenst work rite. I added the FriendID and borrowers Name field to the combo box that selects a movie. I then had the text box look up the correct field in that combo box to display the borrowers name.

Well, it works, and the borrowers name does display in the text box, BUT, now the only movies displayed in the combo box are ONLY the movies that are currently being borrowed!!! lol All other movies that arent being borrowed are not displayed in the combo box... this is definately NOT acceptable.

The combo box needs to display all movies, regardless if they are loaned or not. The borrower Name text box can be blank, if nobody is borrowing that particular movie.

Can someone help me get this DLookup function working? Or provide an alternate solution to displaying who is currently borrowing a movie?

Attached DB below.

 

[jal]

If you have a query called:

qryNameOfBorrower

SELECT B.Name FROM tblBorrowers as B
INNER JOIN tblLoanLIst as L
ON L.FriendID = B.FriendID

Then you should be able to do something like this:

(Name) =DLookUp("[Name]","qryNameOfBorrower","[MovieID]=Forms![frmAddEditMovies]![MovieID]")


or maybe put the WHERE clause in the actual query

SELECT B.Name FROM tblBorrowers as B
INNER JOIN tblLoanLIst as L
ON L.FriendID = B.FriendID
WHERE L.MovieID =Forms![frmAddEditMovies]![MovieID]

and then:

(Name) =DLookUp("[Name]","qryNameOfBorrower","")

Either way should work, I am hoping.

 

[oZone]

Hi, and thanks for the help!

I made a query using the second option you listed and it does work, thanks!

But, I dont understand someting about the DLookup code that you wrote. Could you explain what the "L" and "B" references do exactly? and why L and B?

 

[jal]

Hi, and thanks for the help!

I made a query using the second option you listed and it does work, thanks!

But, I dont understand someting about the DLookup code that you wrote. Could you explain what the "L" and "B" references do exactly? and why L and B?

Aliases are merely shortened table names that save typing. You can use any alias you want, as long it doesn't duplicate a reserved keyword or another table or alias named in the query.

Aliases help you qualify column names. If both tables have the column "Name", I can use B.Name to clarify that I want the B-table's Name. (I could have chosen a different letter, or any combination of letters).This saves me typing out the entire

tblBorrower.Name

Incidentally the problem you were running into is that DLookup doesn't allow you to name to two tables (doesn't accept a join). So I had to create a separate join query and then feed that into Dlookup. Glad it worked, as I don't usually get things right on the first try.

 

[Wet_Blanket]

Since the original problem seems to be fixed, I'll add my similar problem to this thread.

Basically I have a form that includes fields such as account number, first name, last name, address, etc.

I'm hoping to find a way so that when the user enters the account number into the field, the client's information automatically pulls up (from a certain table). I have tried using dlookup, but nothing happens when the account number is entered. Any suggestions?

 

[jal]

I'm hoping to find a way so that when the user enters the account number into the field, the client's information automatically pulls up (from a certain table). I have tried using dlookup, but nothing happens when the account number is entered. Any suggestions?

I can't remember your scenario but here's a suggestion. You should probably drop a button on the form next to the account number textbox. Name the button btnAcctLookup. The user will click this button after typing in the acct number. In the button's OnClick event, you can do your DLookups and, in each case, set the textbox.Value to the result. For instance:

txtBoxName.Value = DLookup("Name", "tablename", "Account=" & txtAccount)

This is just pseudocode obviously, but maybe it will point you in the right direction.

 

[Wet_Blanket]

I can't remember your scenario but here's a suggestion. You should probably drop a button on the form next to the account number textbox. Name the button btnAcctLookup. The user will click this button after typing in the acct number. In the button's OnClick event, you can do your DLookups and, in each case, set the textbox.Value to the result. For instance:

txtBoxName.Value = DLookup("Name", "tablename", "Account=" & txtAccount)

This is just pseudocode obviously, but maybe it will point you in the right direction.

Thank you very much, I believe this puts me in the right direction. One problem I experience is that the form comes up with #deleted in every field, and won't let me update it, and shows this warning:

Microsoft Acces can't find the macro "First Name"....

Here is the code I used:

First Name.Value = DLookup("holder_first_name", "AMS Accounts", "acct_num=" & Account 1)

 

[jal]

I wasn't paying close attention - i thought it was the same poster asking a follow up question.

Thank you very much, I believe this puts me in the right direction. One problem I experience is that the form comes up with #deleted in every field, and won't let me update it, and shows this warning:

Microsoft Acces can't find the macro "First Name"....

Here is the code I used:

First Name.Value = DLookup("holder_first_name", "AMS Accounts", "acct_num=" & Account 1)

I don't understand all those spaces. Your textbox is really called "First Name" with a space? And another textbox with a space called "Account 1" ??? Does that even compile?

I would expect something like this (using brackets to compensate for the spaces)


FirstName.Value = DLookup("holder_first_name", "[AMS Accounts]", "acct_num=" & Account1)

I was assuming unbound textboxes. If your textboxes are bound, my method probably won't work. I had in mind textboxes manually dropped onto the form using the Toolbox, with no binding (no ControlSource specified).

 

[Wet_Blanket]

I wasn't paying close attention - i thought it was the same poster asking a follow up question.



I don't understand all those spaces. Your textbox is really called "First Name" with a space? And another textbox with a space called "Account 1" ??? Does that even compile?

I would expect something like this (using brackets to compensate for the spaces)


FirstName.Value = DLookup("holder_first_name", "[AMS Accounts]", "acct_num=" & Account1)

I was assuming unbound textboxes. If your textboxes are bound, my method probably won't work. I had in mind textboxes manually dropped onto the form using the Toolbox, with no binding (no ControlSource specified).

Well, I've gotten rid of the spacing, but and tried it with an unbound text box, but I still get the error that Access is looking for a macro named Account1. Any idea what that is about?

 

[pbaldy]

Where exactly do you have that code? It sounds like you put it directly in the button's properties window on the click event line. If so, take it out, replace it with [Event Procedure] then click on the ellipsis to the right. That should take you to the VBA editor, which is where that code was intended to be.

As a side note, I'd probably use the after update event of the textbox so the user doesn't have to click on a button.

 

[Wet_Blanket]

Where exactly do you have that code? It sounds like you put it directly in the button's properties window on the click event line. If so, take it out, replace it with [Event Procedure] then click on the ellipsis to the right. That should take you to the VBA editor, which is where that code was intended to be.

As a side note, I'd probably use the after update event of the textbox so the user doesn't have to click on a button.

You are exactly right. But once that VBA editor opened up, I realized how out of my league I am.

This is what is originally in VBA:

Private Sub btnAcctLookup_AfterUpdate()
End Sub

Where do I put?

=DLookUp("holder_first_name","AMS Accounts","acct_num=" & Account1):

 

[pbaldy]

The cursor should have been between those 2 lines, which is where you want that code. Oh and you want to set something equal to that value, so you need something to the left of the equals sign, as in jal's example:

FirstName.Value = DLookup("holder_first_name", "[AMS Accounts]", "acct_num=" & Account1)

and I actually meant the after update event of the textbox you enter the account number in, not the after update event of the button (which doesn't event exist). If you want to use the button, you'd want the click event.

 

[Wet_Blanket]

The cursor should have been between those 2 lines, which is where you want that code. Oh and you want to set something equal to that value, so you need something to the left of the equals sign, as in jal's example:

FirstName.Value = DLookup("holder_first_name", "[AMS Accounts]", "acct_num=" & Account1)

and I actually meant the after update event of the textbox you enter the account number in, not the after update event of the button (which doesn't event exist). If you want to use the button, you'd want the click event.

Alright, I'm getting much closer now. The only error I get is a data type mismatch - which I haven't identified yet. Thanks. As far as I can tell, First.Name / holder_first_name / acct_num / and Account1 are all TEXT. Any suggestions?

 

[pbaldy]

Good reference here on the syntax:

http://www.mvps.org/access/general/gen0018.htm

Yours needs to be adjusted since the value is text.

 

[Wet_Blanket]

Good reference here on the syntax:

http://www.mvps.org/access/general/gen0018.htm

Yours needs to be adjusted since the value is text.

Any chance this looks right to you?

FirstName.Value = DLookup("holder_first_name", "[AMS_Accounts]", "acct_num=" & Account1 & "'")

 

[pbaldy]

Close but no cigar. You missed the hard-to-see single quote right after the equals sign.

 

[Wet_Blanket]

Close but no cigar. You missed the hard-to-see single quote right after the equals sign.

Fantastic - it now works. Thank you soo much. I may have more questions coming.

 

[pbaldy]

No problem. BTW, I would probably use a combo box for the account information that included all the info. That way you avoid the DLookups with:

= Me.ComboName.Column(x)

where x is the column containing the desired info (zero based)

 

[Wet_Blanket]

No problem. BTW, I would probably use a combo box for the account information that included all the info. That way you avoid the DLookups with:

= Me.ComboName.Column(x)

where x is the column containing the desired info (zero based)

I thought about that, would the combo box only populate from the row that Account1 is assigned to?

Another function I need to put in is the ability to populate any related accuonts. Each account of a particular client is assigned a link# to relate them together. Something like:

Link# Account#

657 12345678
657 87654321
657 45687542
127 12349756
127 32146598

Should I just use a combo box for those (there can be upto 6 related accounts)?

 

[Wet_Blanket]

So far what I have for the additional issue:

Account2.Value = DLookup("acct_num", "[AMS_Accounts]", "acc_lnk_id=" & Link_ID)

But how do I make it conditional. I only want it to populate a value for Account2 if it does not equal Account1. Suggestions?