Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Access Combo box; VBA

Posted on 2006-07-23
8
Medium Priority
?
600 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
  • 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
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!

 

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

810 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