Embedding Dynamic Queries in Access Form

[IainMc]

I am trying to create an access form to manage records of photographs in a main table.

I also have about 10 other tables each corresponding to a specific picture library containing records of which images have been sent to them.

In the main form for each image archive record I want to embed a list of these libraries and indicate whether or not the image has been sent to them. Embedding a form for each library is doable but the arrangement isn't really ideal or particularly ergonomic. If an image has not been sent to a specific library I want to add a button which will open the main form for that library to add a new record (otherwise I want the button to be disabled or hidden).

I have a similar function using Asp.net web pages running off IIS on my PC but I want to do the same job entirely in MS Access (for portability) without needing to have IIS running.

In an ASP script I would obviously embed the sql statement in the code and use an If...End If to generate dynamic page content based on whether or not the query returns a result but not sure what the approach would be to do a similar function in an access form.

I'm wondering if there is any way I can call a subroutine/function from within the Private Sub Form_Current() that will execute a query for each image library table in turn, display the relevant information in text labels rather than text boxes and set the button (to open the form for the specific library) status as appropriate.

[Robeen]

I'm wondering if there is any way I can call a subroutine/function from within the Private Sub Form_Current() that will execute a query for each image library table in turn, display the relevant information in text labels rather than text boxes and set the button (to open the form for the specific library) status as appropriate.

I just wanted to zero in on one part of your post [above] - so that I'm not biting off mor than I can chew.

Yes - you CAN call a function from within Private Sub Form_Current() - but you can also just execute your queries right in Private Sub Form_Current() - in line- in a loop.
Or - you can create 'sub subroutine' IN Private Sub Form_Current() something like this [at teh bottom - before your 'End Sub']:

 
RunQueries:
'Put your Code here . . .

Then - in the body of Private Sub Form_Current() - you might have this:

 
GoTo RunQueries

And, based on the results each query returns, you can either open a Form . . . or display information on the current Form.

Can you explain what exactly is the relevant information that you're going to query from each of the libraries?

Putting the query results on Labels rather than TextBoxes is a little more troublesome - simply because by default Access will want to show data in TextBoxes.
But yes - you can write code to make values appear on Labels.

Hope this helps!

[IainMc]

I've created part of a function I need to use abd called it from the Private Sub Form_Current().

The code I have so far is below though I'm not certain what code I need to actually action the query:

Function runStockQueries()

    Dim Library1, Library2 As String
    
    SqlLibrary1 = "SELECT * FROM Library1 WHERE ImageFilename = '" & Me.Filename & "'"
    
SqlLibrary2 = "SELECT * FROM Library2 WHERE ImageFilename = '" & Me.Filename & "'"

[Robeen]

What exactly do you want to do with the information that the SQL fetches?

I see you have a 'Select * . . .'.
Do you need all the fields from Library1, Library2 . . . etc?

[IainMc]

I don't necessarily need to retrieve all fields but there are only about 6 of them and only one record for each filename.

What I want to do is have within the form (which displays the image and the information about it) a dynamic list of the libraries stating whether or not each image has been sent to them. If it hasn't then I want to enable a button to call up a separate form to add a new record to the library table.

[Robeen]

Here's a function that will tell you if you have records in a Table.
You'll have to make changes to fit your Database.

 
Function Get_Row_Count()
'Get number of rows in a table using a query in VBA.
 
Dim db As DAO.Database
Dim rs As DAO.Recordset
 
Dim strSQL As String
Dim intField1 as Integer
 
On Error GoTo Error_Handle
 
Set db = CurrentDb
 
strSQL = "Select Count(*) As NumberOfRecords From [AA_Test]"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
 
With rs
 
Do While Not rs.EOF
    intField1 = rs![NumberOfRecords]
    msgbox strField1
    'Instead of the msgbox - you can put your logic here . . .
    'Eg:
    If intField1 = 0 Then
        'Do something
    End If
.MoveNext                               'Move to next record in recordset.
Loop                                    'Back to 'Do While' to check if we are at the end of the file.
Exit_Get_DB_Values:
    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If
    Set db = Nothing
    Exit Function
Error_Handle:
    Resume Exit_Get_DB_Values
End With
End Function

I hope this helps! Let me know if you have any questions.