Cmd Button based on query related to textbox.

[DJ44]

Here is my problem.

I have a command button (cmdSitePhotos) that is on a subform (xsfrmDataDisp).

When the form/subform is opened, I want a query to be run (I have function executed on the On_Current of the subform properties). The purpose of the query is to seach a table for photos related to that site. If there are photos available for the site, I want the command button to be enabled and for a caption to appear on the button. If there are not photos for that site, then I want the button to be disabled and a different caption to appear.

The Master and Child field- link is via the SiteNum, which I also want to pass to the SQL statement as the parameter.

I have copied the code that I cannot get working. ...Help, please!

Ultimately, if there are photos (and the button is enable, when it is clicked, I want a subform opened that will use the query as its source ---- should I pass the recordset to the new subform or just run the sql statement again?

Thanks in advance, all help is appreciated.

DJ

--------
Private Sub QryPhotos()

On Error GoTo ErrorHandler
Dim rs As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim intFileNum As Integer
Dim lngCount As Long
Dim sSQL As String


Set rs = New ADODB.Recordset
Set cnn = New ADODB.Connection

Set cnn = CurrentProject.Connection

sSQL = "SELECT tblPhoto.SiteNum, tblPhoto.FileName, tblPhoto.PhotoCaption FROM tblPhoto WHERE (((tblPhoto.SiteNum)= [Forms]![xsfrmDataDisp]![tbxSiteNum]))"""

rs.Open sSQL, cnn, adOpenStatic, adLockReadOnly

'Check to see if there are any records

Debug.Print rs.RecordCount

If rs.RecordCount < 1 Then

cmdSitePhotos.Caption = "No Photos Available"
cmdSitePhotos.Enabled = False

Else

cmdSitePhotos.Caption = "View Site Photos"
cmdSitePhotos.Enabled = True

End If

'Close the Recordset and Connection Objects
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing

Exit Sub
ErrorHandler:

Set rs = Nothing
Set cnn = Nothing
Err.Clear

End Sub

--------------------------------

 

[pono1]

DJ,

The thing that jumps out at me while looking at your code is the SQL string. That doesn't mean there aren't other, more difficult-to-find problems that may be affecting your procedure. But you may want to experiment with the string's syntax.

sSQL = "SELECT tblPhoto.SiteNum, tblPhoto.FileName, " & _
	"tblPhoto.PhotoCaption " & _
	"FROM tblPhoto " & _
	"WHERE tblPhoto.SiteNum= " & [Forms]![xsfrmDataDisp]![tbxSiteNum]

Once you get that working you can change the form's record source property using the same SQL string if there are recs found. But, of course, first things first.

Regards,
Tim

 

[DJ44]

Thanks for the suggestion, Tim.

I am fairly new to using code so I did a cut and paste from the SQL view of the Query Builder.

To isolate the code, I created a new database and copied the two related tables to it; created a simple form with a textbox (tbxSiteNum) and a command button (cmdSitePhotos) and included the following code:

Option Compare Database
Option Explicit

Private Sub Form_Current()

Dim iSiteNumber As Integer

    Me!tbxSiteNum = iSiteNumber

CheckForPhotos iSiteNumber

End Sub


Private Function CheckForPhotos(iSiteNumber As Integer)

On Error GoTo ErrorHandler
    Dim rs As ADODB.Recordset
    Dim cnn As ADODB.Connection
    Dim intFileNum As Integer
    Dim lngCount As Long
    Dim sSQL As String
    
       
    Set rs = New ADODB.Recordset
    Set cnn = New ADODB.Connection
    
    Set cnn = CurrentProject.Connection
    
    
    
    sSQL = "SELECT tblPhoto.SiteNum, tblPhoto.FileName, " & _
            "tblPhoto.PhotoCaption " & _
            "FROM tblPhoto " & _
             "WHERE tblPhoto.SiteNum= " & iSiteNumber
        
    rs.Open.sSQL , cnn, adOpenStatic, adLockReadOnly
    
    'Check to see if there are any records
    
    Debug.Print rs.RecordCount
    
        If rs.RecordCount < 1 Then
        
            Me.cmdSitePhotos.Caption = "No Photos Available"
            Me.cmdSitePhotos.Enabled = False
            
            Else
            
            Me.cmdSitePhotos.Caption = "View Site Photos"
            Me.cmdSitePhotos.Enabled = True
        
        End If
    
    'Close the Recordset and Connection Objects
    rs.Close
    cnn.Close
    Set rs = Nothing
    Set cnn = Nothing

Exit Function
ErrorHandler:

 Set rs = Nothing
 Set cnn = Nothing
 Err.Clear
 
End Function

It "works" for the first record, however the SiteNum field shows as 0 (when it should be the first record in the table (10010). When I move to the next record, SiteNum remained 0 (or was changed to zero???) and then I could not move to another record without the error meassage that I could not duplicate a value of a primary key (which the SiteNum field is).

Why is this happening??

Thanks. DJ

 

[DJ44]

Tim:

Thanks for the reply.

I already have the tables and relationships. I have created a simple database just as you suggested with a table for sites and another for photos.

What I ultimately want to accomplish is display the photos for a particular site. I originally thought it would be a simple subform, but have since learned that displying photos using access is not an easy task, espcially if they are not embedded. I have a DB with 500 sites and close to 2000 photos.

So .... what I wanted to accomplish in the original post was this:

I created a form that has two controls:

A textbox (tbxSiteNum) that displays the Site Number form the tblSite; and
A command button (cmdSitePhotos).

What I want to is, when the frm is opened, or when the record is advanced (thru the sitenum) ; the query in the SQL statement (which is correct) will execute. If the query returns atleast one record (i.e. there are photos for that site), then I want the command button to be active (which I think means is able to be clicked) and the following caption to appear "View Site Photos".

If there are no photos for the site, then I want the command button to be inactive (can't be clicked) and the capiton "No site photos available" to appear on the button.


If there are photos, and the button is clicked, I have another form the will load the photos for that site, using the SQL statement I used in this code as the record source.

I hope this is clearer. I have attached a more recent version of my code but it still is not doing what I want.

Thanks again for you patience.

DJ

Private Sub cmdSitePhotos_Click()

On Error GoTo Err_cmdSitePhotos_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmDisplayImg"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdSitePhotos_Click:
    Exit Sub

Err_cmdSitePhotos_Click:
    MsgBox Err.Description
    Resume Exit_cmdSitePhotos_Click
    
End Sub



Private Sub Form_AfterUpdate()

Dim iSiteNumber As Integer

    iSiteNumber = Me.tbxSiteNum
    
CheckForPhotos iSiteNumber


End Sub

Private Sub Form_Current()

Dim iSiteNumber As Integer

    iSiteNumber = Me.tbxSiteNum
    
CheckForPhotos iSiteNumber

End Sub


Private Function CheckForPhotos(iSiteNumber As Integer)

On Error GoTo ErrorHandler
    Dim rs As ADODB.Recordset
    Dim cnn As ADODB.Connection
    Dim sSQL As String
    
       
    Set rs = New ADODB.Recordset
    Set cnn = New ADODB.Connection
    
    Set cnn = CurrentProject.Connection
    
    
    
    sSQL = "SELECT tblPhoto.SiteNum, tblPhoto.FileName, " & _
            "tblPhoto.PhotoCaption " & _
            "FROM tblPhoto " & _
             "WHERE tblPhoto.SiteNum= " & iSiteNumber
        
    rs.Open sSQL, cnn, adOpenStatic, adLockReadOnly
    
    'Check to see if there are any records
    
    Debug.Print rs.RecordCount
    
        If rs.RecordCount < 1 Then
        
            Me.cmdSitePhotos.Caption = "No Photos Available"
            Me.cmdSitePhotos.Enabled = False
            
            Else
            
            Me.cmdSitePhotos.Caption = "View Site Photos"
            Me.cmdSitePhotos.Enabled = True
        
        End If
    
ErrorHandler:

 Set rs = Nothing
 Set cnn = Nothing
 Err.Clear
     
End Function

 

[DJ44]

SUCCESS!!!

Many,many, thanks Tim.

I think I am finally understanding the basics of writing and structuring VBA.

Cheers, DJ