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
Solved

Access Combo box; VBA

Posted on 2006-07-23
8
583 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

839 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