Subform help

[TVReplay99]

I think I am having a serious brain fart.
I have a sipmle Order form and Order Deatils subform. The order for works fine and so does the subform, sorta. I have not been able to populate the UnitPrice field on the subform after I select the Item from the StockNo combo box.

The form is based on this Query:
SELECT
tblTransDetail.TransasctionNo,
tblTransDetail.StockNo,
tblTransDetail.TransQuantity,
tblTransDetail.TransUnitPrice,
tblStock.Desc,
[TransQuantity]*[TransUnitPrice] AS LineTotal
FROM tblStock INNER JOIN tblTransDetail ON tblStock.StockNo = tblTransDetail.StockNo;

I have tried this Code but to no avail.

Private Sub StockNo_AfterUpdate()
On Error GoTo Err_StockNo_AfterUpdate
Dim strFilter As String
strFilter = "StockNo = " & Me!StockNo
Me!TransUnitPrice = DLookup("Price", "tblStock", strFilter)
Exit_StockNo_AfterUpdate:
Exit Sub
Err_StockNo_AfterUpdate:
MsgBox Err.Description
Resume Exit_StockNo_AfterUpdate
End Sub

Thank you for any assistance.

 

[Pat Hartman]

Your code looks ok although I never use DLookup() for this purpose. If StockNo is text, you need to surround it with quotes.

I include the price field in the combo where you choose the StockNo. Then in the AfterUpdate event of the combo:

Me.TransUnitPrice = Me.StockNo.Column(2)

The columns of a combo are zero based strings so the code above assumes that price is the third field. If it is the second, use .Column(1).

 

[TVReplay99]

Pat,
Thank you for wour wonderful insight, it workes perfectly. I am courious thought. I am courious exactly when to use Dlookup()? I have serched the help files but achieved a minor understanding and more confussion, as my previous post will atest to.

Thanks again you ended 2 days of frustration.

Brian

 

[Pat Hartman]

You would use DLookup() in a place where you need a function and a query with a join won't solve the problem. It's late and I can't think of a good example. However, I can tell you when I stopped using DLookup() entirely. I wrote a code loop that updated 100,000 records as part of a conversion and used a DLookup() to convert a value from what was used in applicationA to what would be used in the new application. The process took 1.5 hours to run. I removed the DLookup() and incorporated the lookup table in my query with a join and the processing time was reduced to under 10 minutes. If you think about it, it makes sense. The lookup table contained 45 rows so each of the 100,000 records needed to read the lookup table which made 100,000 reads against the main table and 4.5 MILLION reads against the lookup table.

Access help uses DLookup() frequently in its examples so people think that it is the preferred method. If your tables are small it doesn't matter what you do but if your application is processing any amount of data at all, they are way too inefficient to be used when a query with a join will suffice.

 

[TVReplay99]

Pat
Thanks for your reply, I have tried many times to use DLookup and have NEVER been successful. My new problem that I have searched this site for answers on is Password verification. Almost all searches have led me back to a DLookup function.

All I need is to link a password to a user and then a user to pick which account to bill. It is a basic transaction form they need access to but to get to it they have to enter a password.

I have chosen not to use Access Security due to the end users who will have to enter and maintain the database.

The code I have tried with no success is:

Private Sub Password_AfterUpdate()

If IsNull(DLookup("[Password]", "tblCustomer", "[Password] = " & Me.[Password])) Then
MsgBox "Invalid Password.", vbOKOnly, "Password"
Me![Password].SetFocus
Me![Password] = ""
Exit Sub
End If

DoCmd.OpenForm "frmTransactions"

End Sub

All I need it to do is if the password is correct (Not using a UserID, and no duplicate Passwords allowed) open the From.

Thanks for any reply.

Brian

P.S. I am not using Developer, is their any setting I have to change to be able to use junctions such as DLookup?

 

[Pat Hartman]

The problem is that password is a text field and so it needs to be enclosed in quotes:

If IsNull(DLookup("[Password]", "tblCustomer", "[Password] = " & chr(034) & Me.[Password] & chr(034))) Then

 

[TVReplay99]

Pat
I thought on one of my many tries, I had Quotes around everything that was a text field but I guess I was wrong. Thank you a ton.

One question. What is Chr(34) for? In all my searches on this topic I have not seen this before.

Thanks again.

Brian

 

[Pat Hartman]

It is the simplest way to surround a text item with double quotes. If you want to use single quotes:

If IsNull(DLookup("[Password]", "tblCustomer", "[Password] = '" & Me.[Password] & "'")) Then

 

[directormac]

Brian: Another trick lots of folks use is to declare a public constant at the top of one of their modules:

Public Const QUOTE As String = """"

Then you can use this everywhere you need to add quote literals within a string:

If IsNull(DLookup("[Password]", "tblCustomer", "[Password] = " & QUOTE & Me.[Password] & QUOTE))

Actually, I may have learned that from Pat...

--Tricky Mac

 

[Pat Hartman]

You did. That is the method that I actually use

 

[directormac]

Me Love Pat

Actually, I may have learned that from Pat...

You did. That is the method that I actually use

Of course, that's true of about 90% of what I know...

 

[directormac]

Public Thanks

...and another 9.9% coming from the rest of the forum gurus, who (IMNSHO) don't get enough gratitude. My deep thanks and appreciation to all those who have helped me and everyone else so much around here, including (but not limited to):

Mile-O-Phile
Rich
Collin Essex
The_Doc_Man
neileg
mission2java
AutoEng
Vassago
Haley Baxter
ghudson

and many, many more. Whenever anyone asks me an Access question I always give them the addy for the forum and tell them it's the most helpful place on the web I've ever found (on any topic). So to all you helpful types:

THANK YOU

--Grateful Mac