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.

 

[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

 

[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?

 

[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

 

[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

 

[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