Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Access Combo box; VBA

Posted on 2006-07-23
8
Medium Priority
?
595 Views
Last Modified: 2011-10-03
Access DB
TblRes
ID   Room              Name
1    luxury               abc
2    semi-luxury       xyz
3    special             pqr


Form

Date : [_____________]      text box
Room :[_____________[v]   combo box filled with [Room] Field of TblRes table
           
              [Lookup]                button

 when i run following code, it give me error message
"You can't reference a property or method for a control unless the control has the fucus. 2185"


Private Sub cmdLookup_Click()

On Error GoTo Err_cmdLookup_Click

strrd = Me!tfResDate.Value
strRoom = Me!cbRoom.Text

    ' it works
    'Set ObjRs = CurrentDb.OpenRecordset("SELECT * FROM TblRes WHERE ResDate = '" & strrd & "' ")
   
    'it does not work
    Set ObjRs = CurrentDb.OpenRecordset("SELECT * FROM TblRes WHERE ResDate = '" & strrd & "' AND

Room = '" & strRoom & "' ")
    If ObjRs.EOF Then
        MsgBox "Not Found :  " & strLogin, vbOKOnly + vbInformation, "Error..."
    Else
       MsgBox "Found", vbOKOnly + vbInformation, "Success"
       
    End If
       
ObjRs.Close



Exit_cmdLookup_Click:
    Exit Sub

Err_cmdLookup_Click:
    If Err.Number = 94 Then
        Resume Next
    Else
        MsgBox Err.Description & "" & vbCrLf & Err.Number
        Resume Exit_cmdLookup_Click
    End If
End
0
Comment
Question by:aspbank
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 17162607
Strange that the first does work, as I assume you're using a date and would need:
Set ObjRs = CurrentDb.OpenRecordset("SELECT * FROM TblRes WHERE ResDate = #" & strrd & "# ")
when outside the US, better to use:
Set ObjRs = CurrentDb.OpenRecordset("SELECT * FROM TblRes WHERE ResDate = #" & format(strrd,"yyyy/mm/dd) & "# ")

Nic;o)
0
 

Author Comment

by:aspbank
ID: 17162658
i want to run this query atleast

Set ObjRs = CurrentDb.OpenRecordset("SELECT * FROM TblRes WHERE Room = '" & strRoom & "' ")
0
 
LVL 54

Expert Comment

by:nico5038
ID: 17162669
That should work when you use the proper name of the Room field from the form. (Check the name under properties popup under the Other tab) and use:

dim ObjRs as DAO.recordset

Set ObjRs = CurrentDb.OpenRecordset("SELECT * FROM TblRes WHERE Room = '" & Me!cbRoom & "' ")
IF ObjRs.eof and ObjRs.bof then
    ' not found
else
   ' found
endif

Nic;o)
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:aspbank
ID: 17163016
i know that should work but it is not working thats why i am here
combo box name is exactly cbRoom
 
i dont know why this error message comes
"You can't reference a property or method for a control unless the control has the fucus. 2185" ?
0
 
LVL 20

Accepted Solution

by:
Gawai earned 500 total points
ID: 17163374
just add one line  Me!cbRoom.SetFocus


Private Sub cmdLookup_Click()

On Error GoTo Err_cmdLookup_Click

Me!cbRoom.SetFocus







let me know
0
 
LVL 54

Expert Comment

by:nico5038
ID: 17163560
Change:
strRoom = Me!cbRoom.Text
into
strRoom = Me!cbRoom

Nic;o)
0
 

Author Comment

by:aspbank
ID: 17166037
@ gawai it works great thanks  @nico thanks for your response
0
 
LVL 54

Expert Comment

by:nico5038
ID: 17169557
Did you try to remove the .text ?
It's uggly programming to add statements to suppress error messages as it hard to follow in the future by you and/or other programmers that do maintenance.

Nic;o)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question