INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

DLookup Problems

DLookup Problems

(OP)
I am building a database for a barbershop. It's not that complicated of a database but I am having problems with the DLookup function. If anyone can help me I'd be greatly appreciative. Thank you in advance! I have an unbound form that will be used to select a customer (or add new one), and then select the type of haircut by checking a checkbox that reflects what the customer got (regular, style, shave etc). Next to the each checkbox I have a text box that I want to reflect how much that haircut is. I am trying to use the DLookup in the control source for each text box that will pull back a value from the "tblHaircutType" which has each type of haircut with it's price. In this table there are only HaircutType, TypeName, TypePrice, fields. My expression in the DLookup is as follows:

=DLookUp("[TypePrice]","tblHaircutType","[HaircutType] = 1")

HaircutType TypeName TypePrice
1 Style 15.00
2 Regular 12.00
3 Shave 5.00

I can't figure it out...Any and all help would be appreciated!

RE: DLookup Problems

You don't need the [ ]. Is HaircutType text or Number?
If text
=DLookUp("TypePrice","tblHaircutType","HaircutType = '1'")
If Number
=DLookUp("TypePrice","tblHaircutType","HaircutType = " & 1)

RE: DLookup Problems

(OP)
The HaircutType is an autonumber field, primary key. I tried it either way but no good. I changed the criteria to look for "Style" and it worked fine. Here's my code:
=DLookUp("TypePrice","tblHaircutType","TypeName = 'Style'")
Thanks for your help...I will maybe need more once i get to writing code, right now I am just doing the infrastructure and placing controls to work with...Thank You!!

RE: DLookup Problems

(OP)
I do have a question on another issue and I was hoping you'd point me in the right direction. On this unbound form I have 8 unbound check boxes that user will select when entering the haircut info into the table. User can only select one of the 8 check boxes because each check box is for each type of haircut. How can I run an after update on each of the check boxes to ensure only one is selected? I would like to create a private/public sub to run after each check box is selected by calling it but I can't remember how to do that. I want to do it the right way, not have a hundred If statements and your help would be greatly appreciated! I have a pretty good knowledge of VBA but it's been a few years since I've written any code, so please bear with me...

RE: DLookup Problems

Don't use check boxes, use option buttons (also called radio buttons)

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: DLookup Problems

Quote:

Don't use check boxes, use option buttons (also called radio buttons)
As stated that is incorrect. Their is a radio button control near the checkbox control and that will create a single radio button. Would be the exact same problem as picking a checkbox control. You need to pick the Option Group control. That is the seventh control from the left.

RE: DLookup Problems

(OP)
Hello MajP, you helped me a couple years ago with a database I was building for a restaurant...Yes, I am using radio buttons, and I have them all laid out and properly named. I just need direction on how to go about writing a procedure to check all radio buttons' value and make sure that only one is selected and that there is one selected. I just can't remember how to do that. I don't need the code written for me, I just need little direction and I am greatly appreciative! Thank you!

RE: DLookup Problems

That is my point you use the option group control, and it puts all of your buttons in a frame and ensures that only one item can be picked. No code required.

The blue arrow is the Option Group which will create a bunch of "related" radio buttons. The red is the single radio button control. Delete your current radio buttons and use the Option Group to create new buttons.

RE: DLookup Problems

With an option group you can have checkboxes, toggles, or radio buttons. The code is then on the afterupdate of the frame you never deal with the individual items in the group.

CODE

Private Sub Frame0_AfterUpdate()
  Dim typeName As String
  Select Case (Frame0.Value)
    Case 1
      typeName = "Style"
    Case 2
      typeName = "Reqular"
    Case 3
      typeName = "Shave"
  End Select
  Me.txtCost = DLookup("TypePrice", "tblHaircutType", "TypeName = '" & typeName & "'")
End Sub 

RE: DLookup Problems

(OP)
I did like you said and it worked out great...thank you very much!

RE: DLookup Problems

(OP)
I am having a problem with running a SQL statement. I've dimmed all my variables and it does exactly what I want it to do but I am having a problem with the date variable. It enters it into the table but it's 12/30/1899...I know I have to use # signs but I don't know where to put them in my VBA code. Do I surround the variable with them? Or do I put them in the SQL Statement? I've searched help files and can't find the answer...any help would be greatly appreciated...thanks.

Here's my code:

Dim strSQL As String, haircutDate As Date, paymentFrame As String, haircutFrame As String
Dim payType As Integer, cost As Currency, cust As Integer, emp As Integer

haircutDate = Me.txtHaircutDate.Value
paymentFrame = Me.FramePayment.Value
haircutFrame = Me.FrameHaircutStyles.Value
payType = Me.txtPaymentType.Value
cost = Me.txtHaircutCost.Value
cust = Me.cboCustomerName.Column(0)
emp = Me.cboEmployeeName.Column(0)

strSQL = "INSERT INTO tblHaircuts (CustID,HaircutType,HaircutDate,TransactionType,EmployeeID, TotalPrice) VALUES (" & cust & "," & haircutFrame & "," & haircutDate & "," & paymentFrame & "," & emp & "," & cost & ");"


DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

RE: DLookup Problems

Use this function

CODE

Function SQLDate(varDate As Variant) As string
     If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function 

then
haircutDate = SQLDate(Me.txtHaircutDate.Value)

RE: DLookup Problems

Examples
?SqlDate(now)
#09/15/2016 10:08:04#

?sqlDate(date)
#09/15/2016#

RE: DLookup Problems

You have this:
Dim haircutDate As Date
and then you assign the value (text) to your variable hoping the correct conversion takes place:
haircutDate = Me.txtHaircutDate.Value
You may want to do this:
haircutDate = CDate(Me.txtHaircutDate.Value)

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: DLookup Problems

If you are doing an insert query I would recommend making it a string and using that format function. This will work regardless of regional settings (i.e. European dates dd/mm/yy)

RE: DLookup Problems

(OP)
I tried both ways just to learn, but the SQLDate Function was the only way I could get it to work. I also coded number signs around my variable in the sql statement and that worked as well but I like the function so I don't have to write the statement every time...Thanks for all the help MajP and Andrzejek! I really appreciate it. I might be back soon, I have some ideas for some cool functionality I want to try lol...Thanks again. Dannie.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close