Solved

Access Combo box; VBA

Posted on 2006-07-23
8
578 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

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.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
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…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

786 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