DLookup Problems 2

[DBServices]

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!

 

[LaurieHamlin]

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

 

[DBServices]

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!!

 

[DBServices]

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...

 

[Andrzejek]

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

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[MajP]

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.

 

[DBServices]

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!

 

[MajP]

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.

 

[MajP]

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.

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

 

[DBServices]

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

 

[DBServices]

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

 

[MajP]

Use this function

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)

 

[MajP]

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

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

 

[Andrzejek]

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

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[MajP]

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)

 

[DBServices]

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.