?
Solved

Access Combo box; VBA

Posted on 2006-07-23
8
Medium Priority
?
592 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

752 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