asked on

Access VBA error 3079

I have a table tblMedia which is linked via a junction table tbljctMediaTask to a table tblTask to create a many-to-many relationship.

Both tblMedia and tbljctMediaTask have a field called MediaID (used to link them).

Using VBA, I construct a SQL source for a form as follows:

strSQL = "SELECT qrymedia.mediaselector AS Selector, qrymedia.mediaID AS LinkedID, tbljctMediaTask.*, qrymedia.*" & _
" FROM tbljctMediaTask LEFT JOIN qrymedia ON tbljctMediaTask.mediaID = qrymedia.mediaID" & _
" WHERE tbljctMediaTask.TaskID = " & plngTaskID  & " ORDER BY qrymedia.mediaSelector;"

Open in new window

where qryMedia is predefined (using Create Query) as follows:

SELECT tblMedia.*, tblMedia.MediaName AS MediaSelector, *
FROM tblMedia
ORDER BY tblMedia.MediaName;

Open in new window

and plngTaskID is a long variable identifying a specific unique record in tblTask (the other side of the many-to-many relationship).

I open the frmMedia form (in ctlSubform), set the recordsource to the constructed SQL (during the form open event) and then try to move to a specific record (where MediaID = lngNodeID) using this code:

dim rst as dao.recordset
     Set rst = ctlSubform.Form.RecordsetClone
     With rst
         If lngNodeID = -1 Then
             'First record is wanted
             If Not .BOF And Not .EOF Then
                 .MoveFirst
                 ctlSubform.Form.Bookmark = rst.Bookmark
             End If
         Else
             'Specific record is wanted       
             rst.FindFirst "[MediaID] = " & lngNodeID
             If Not rst.NoMatch Then
                 ctlSubform.Form.Bookmark = rst.Bookmark
             End If
         End If
     End With
     rst.Close
     
PROC_EXIT:
     Set rst = Nothing

Open in new window

An error 3079 results.

The same code (which is parameterised to change the data types on both sides of the many-to-many relationship) to construct the SQL source for the form and move to a given record works for 36 other junction tables/data tables with the same structures -- e.g. table tblNote linked via tbljctNoteTask to tblTask with fields TaskID, opening a subform and moving to a specific value of NoteID. It only only fails for combinations involving tblMedia/qryMedia.

I have (without success):

Turned NameAutoCorrect Off -- still get error 3079

Recreated qryMedia from scratch -- still get error 3079

Changed the findfirst statement to look for qryMedia.MediaID (or qryNote.NoteID etc) -- this gives an error 3345 in all cases, not just for the Media

Changed the findfirst statement to look for tblMedia.MediaID etc -- this also gives an error 3345 in all cases

Removed "qrymedia.mediaID AS LinkedID," from the query -- still get error 3079

Changed the findfirst statement to rst.FindFirst "[LinkedID] = " & lngNodeID. The form moves to the right record but breaks with an error 2424 when I refer to the MasterID field (which is populated from MediaID/NoteID etc.)

Recreated the entire front-end database from scratch -- still get error 3079

Recreated tblMedia and tbljctMediaTask in the backend database from scratch-- still get error 3079

What else should I try?

I can't upload the database -- it depends on a third-party tree control which I shouldn't make available except in runtime.

If there was a way to award more than 500 points for this, I would!

Rey Obrero (Capricorn1)

which line is raising the error ?

first, change your query

SELECT tblMedia.*, tblMedia.MediaName AS MediaSelector, *
FROM tblMedia
ORDER BY tblMedia.MediaName;

with

SELECT tblMedia.*, tblMedia.MediaName AS MediaSelector
FROM tblMedia
ORDER BY tblMedia.MediaName;

colevalleygirl

ASKER

The error is raised on the Findfirst line.

If I follow your suggestion, the code to move to a specific record works, but I get an error 2424 when I refer to a field MasterID on the form (bound to MediaID) during the first Current event. If I change the binding for this field to qryMedia.MediaID I get error 2424 if the form is opened with recordsource = qrymedia, and error 3079 when I execute the code to move to a specific record with recordsource as specified in the original problem statement.

Rey Obrero (Capricorn1)

run this query

SELECT tblMedia.*
FROM tblMedia

ccopy the first line include the headers and post here

run this query

select tbljctMediaTask.*
from tbljctMediaTask

copy the first line include the headers and post here

colevalleygirl

ASKER

Media query:

Media ID      Name       File
1        new media      C:\Users\Helen\Desktop\Ijan.jpg

Junction table query:

ID      TaskID      MediaID
1      89        61

Changing the caption in the Media table from Media ID to MediaID doesn't help; still error 3079.

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

colevalleygirl

ASKER

Thank you! This *seems* to work (seems because I've been struggling with it so long, I can hardly believe it):

SELECT tblMedia.[MediaName] as Selector, tblMedia.[MediaName], tblMedia.[MediaID] as LinkedId, tblMedia.MediaFile, " & strJunctionTable & ".* " & _
"FROM " & strJunctionTable & " LEFT JOIN tblmedia ON " & strJunctionTable & ".mediaID = tblmedia.mediaID " & _
"WHERE " & strMasterIDfield & " = " & plngMasterID & strItemCriteria & _
"ORDER BY tblMedia.[MediaName];

colevalleygirl

ASKER

Wonderful! Thank you!