Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 267
  • Last Modified:

A couple of VBA issues

Hello all

Am struggling with my little foray into Access VBA, 2 issues have arisen.

I have a search list box (lst_SongResults) in a form (frm_SongSearch) which is supposed to update when you do a search.  I've tested with the query alone and this works.

Code for this is:
--
Private Sub txt_SongArtNameQry_AfterUpdate()

Forms!frm_SongSearch!lst_SongResults.RowSource = "SELECT tbl_Artists.ArtistName, tbl_Songs.ISRC, tbl_Songs.TrackName, tbl_Albums.AlbumName, tbl_Songs.[AlbumTrack#], tbl_Songs.Single FROM tbl_Artists INNER JOIN tbl_Albums ON tbl_Artists.ArtistID = tbl_Albums.ArtistID INNER JOIN tbl_Songs ON tbl_Albums.ASIN = tbl_Songs.ASIN WHERE tbl_Artists.ArtistName Like '*" & Forms!frm_SongSearch!txt_SongArtNameQry.Value & "*';"

Forms!frm_SongSearch!lst_SongResults.Requery

Forms!frm_SongSearch.Repaint

End Sub
--

Additionally, I have a form to create an album, the form pulls in existing artist information in a Combo box which works perfectly - in fact it adds the Album to tbl_Albums perfectly, however it also ends up re-adding the arts to the end of the tbl_Artists table when it's unneccesary.

Code for this is:
--
Private Sub cmd_AddAlbSave_Click()
On Error GoTo Err_cmd_AddAlbSave_Click

    DoCmd.GoToRecord , , acNewRec

Exit_cmd_AddAlbSave_Click:
    Exit Sub

Err_cmd_AddAlbSave_Click:
    MsgBox Err.Description
    Resume Exit_cmd_AddAlbSave_Click
   
End Sub
--

Any advice on these would be most appreciative.

Thanks in advance!
0
CSerpent
Asked:
CSerpent
  • 12
  • 10
  • 5
1 Solution
 
MINDSUPERBCommented:

For the first issues, try to change this part of code: Like '*" & Forms!frm_SongSearch!txt_SongArtNameQry.Value & "*' into

Like "*" & Forms!frm_SongSearch!txt_SongArtNameQry.Value & "*"

For issue number 2, check the relationships of your table. Do check as the record source of your form.

Sincerely,
Ed
0
 
MINDSUPERBCommented:
It should mean as: Do check as well the record source of your form.
0
 
Rey Obrero (Capricorn1)Commented:
1. there is nothing wrong with your codes

the afterupdate event of the the textbox will fire when you tab out of the textbox,

try again, type an info in the textbox and hit the tab key.



1a. you don't need this line
        Forms!frm_SongSearch!lst_SongResults.Requery
    when you are setting the rowsource of a combo/list box in vba.

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
CSerpentAuthor Commented:
Ed, thanks... Tried the first one, but that mod just ends up in a type mismatch code 13 error.

The relationships do seem fine, as well as the record source for the form:

SELECT tbl_Albums.ASIN, tbl_Albums.AlbumName, tbl_Albums.ArtistID, tbl_Albums.Format, tbl_Albums.Genre, tbl_Albums.AlbumDate, tbl_Albums.Tracks, tbl_Albums.ProducerID, tbl_Albums.StudioID, tbl_Producers.ProducerName, tbl_Studio.StudioName, tbl_Artists.ArtistName
FROM tbl_Studio INNER JOIN (tbl_Producers INNER JOIN (tbl_Albums INNER JOIN tbl_Artists ON tbl_Albums.ArtistID = tbl_Artists.ArtistID) ON tbl_Producers.ProducerID = tbl_Albums.ProducerID) ON tbl_Studio.StudioID = tbl_Albums.StudioID;
0
 
Rey Obrero (Capricorn1)Commented:
in most cases, it is preferable that you use a command Button click event to do the searching..and if the listbox is on the same form, use "Me" instead of  "Forms!frm_SongSearch"

private sub btnSearch_click()
if len(me.txt_SongArtNameQry & "")>0 then

Me.lst_SongResults.RowSource = "SELECT tbl_Artists.ArtistName, tbl_Songs.ISRC, tbl_Songs.TrackName, tbl_Albums.AlbumName, tbl_Songs.[AlbumTrack#], tbl_Songs.Single FROM tbl_Artists INNER JOIN tbl_Albums ON tbl_Artists.ArtistID = tbl_Albums.ArtistID INNER JOIN tbl_Songs ON tbl_Albums.ASIN = tbl_Songs.ASIN WHERE tbl_Artists.ArtistName Like '*" & Me.txt_SongArtNameQry.Value & "*';"


Me.Repaint
else
msgbox "Please enter item to search"
me.txt_SongArtNameQry .setfocus
exit sub
end if
End Sub
0
 
CSerpentAuthor Commented:
Capricorn, thanks...

When tried, in this case entering "The Beatles" or "beat" for example which are most definitely in the table results in the table being emptied.  I've succesfully had this work on another form.  I want to try and keep this as an after update versus using a command button.

0
 
Rey Obrero (Capricorn1)Commented:
can you try running this as a query

SELECT tbl_Artists.ArtistName, tbl_Songs.ISRC, tbl_Songs.TrackName, tbl_Albums.AlbumName, tbl_Songs.[AlbumTrack#], tbl_Songs.Single FROM tbl_Artists INNER JOIN tbl_Albums ON tbl_Artists.ArtistID = tbl_Albums.ArtistID INNER JOIN tbl_Songs ON tbl_Albums.ASIN = tbl_Songs.ASIN WHERE tbl_Artists.ArtistName Like "*beat*"
0
 
Rey Obrero (Capricorn1)Commented:
you said "I've succesfully had this work on another form. "

can you upload a copy of the db you are working.  upload a .mdb version
0
 
CSerpentAuthor Commented:
That gives me a syntax error on a missing operator.

I'm happy to post the bit of code, but I'd rather not post the MDB unless I can delete afterwards.

Code from other working form is:
Private Sub txt_AlbumArtNameQry_AfterUpdate()

Forms!frm_AlbumsInt!lst_AlbumResults.RowSource = "SELECT tbl_Artists.ArtistName, tbl_Albums.ASIN, tbl_Albums.AlbumName, tbl_Albums.Genre, tbl_Albums.Format, tbl_Albums.AlbumDate, tbl_Albums.Tracks, tbl_Albums.ProducerID FROM tbl_Artists INNER JOIN tbl_Albums ON tbl_Artists.ArtistID = tbl_Albums.ArtistID WHERE tbl_Artists.ArtistName LIKE '*" & Forms!frm_AlbumsInt!txt_AlbumArtNameQry.Value & "*';"

Forms!frm_AlbumsInt!lst_AlbumResults.Requery

Forms!frm_AlbumsInt.Repaint

End Sub
0
 
Rey Obrero (Capricorn1)Commented:
ok, try running this query, without the where clause


SELECT tbl_Artists.ArtistName, tbl_Songs.ISRC, tbl_Songs.TrackName, tbl_Albums.AlbumName, tbl_Songs.[AlbumTrack#], tbl_Songs.Single
FROM tbl_Artists INNER JOIN tbl_Albums ON tbl_Artists.ArtistID = tbl_Albums.ArtistID
INNER JOIN tbl_Songs ON tbl_Albums.ASIN = tbl_Songs.ASIN

see if you get results
0
 
CSerpentAuthor Commented:
Syntax error (missing operator) in query expression 'tbl_Artists.ArtistID = tbl_Albums.ArtistID INNER JOIN tbl_Songs on tbl_Albums.ASIN = tbl_Songs.ASI'
0
 
MINDSUPERBCommented:
Have a check the Field types of the ASIN field on both tables.

Ed
0
 
CSerpentAuthor Commented:
AutoNumber and Number respectively
0
 
MINDSUPERBCommented:
Can you post the screenshot of the said query in design view? You may create a new query and use that statement.

Ed
0
 
Rey Obrero (Capricorn1)Commented:
<Syntax error (missing operator) in query expression 'tbl_Artists.ArtistID = tbl_Albums.ArtistID INNER JOIN tbl_Songs on tbl_Albums.ASIN = tbl_Songs.ASI'>

then i suggest that you make the query work first with the joins that you are trying to use..


that is why is suggested that you upload a copy of the db, so we can check the reason why your query does not work..
0
 
CSerpentAuthor Commented:
@capricorn1: - Ed this may also assist your response.

I'd taken the code from a query that does work:
SELECT tbl_Artists.ArtistName, tbl_Songs.ISRC, tbl_Songs.TrackName, tbl_Albums.AlbumName, tbl_Songs.[AlbumTrack#], tbl_Songs.Single
FROM (tbl_Artists INNER JOIN tbl_Albums ON tbl_Artists.ArtistID = tbl_Albums.ArtistID) INNER JOIN tbl_Songs ON tbl_Albums.ASIN = tbl_Songs.ASIN
WHERE (((tbl_Artists.ArtistName) Like "*" & [Enter Artist Name] & "*"));
0
 
Rey Obrero (Capricorn1)Commented:
ok , you remove the parentheses form the join


us this then


Me.lst_SongResults.RowSource = "SELECT tbl_Artists.ArtistName, tbl_Songs.ISRC, tbl_Songs.TrackName, tbl_Albums.AlbumName, tbl_Songs.[AlbumTrack#], tbl_Songs.Single
FROM (tbl_Artists INNER JOIN tbl_Albums ON tbl_Artists.ArtistID = tbl_Albums.ArtistID) INNER JOIN tbl_Songs ON tbl_Albums.ASIN = tbl_Songs.ASIN
WHERE tbl_Artists.ArtistName Like '*" & Me.txt_SongArtNameQry.Value & "*'"
0
 
Rey Obrero (Capricorn1)Commented:
Private Sub txt_SongArtNameQry_AfterUpdate()

Me.lst_SongResults.RowSource = "SELECT tbl_Artists.ArtistName, tbl_Songs.ISRC, tbl_Songs.TrackName, tbl_Albums.AlbumName, tbl_Songs.[AlbumTrack#], tbl_Songs.Single
FROM (tbl_Artists INNER JOIN tbl_Albums ON tbl_Artists.ArtistID = tbl_Albums.ArtistID) INNER JOIN tbl_Songs ON tbl_Albums.ASIN = tbl_Songs.ASIN
WHERE tbl_Artists.ArtistName Like '*" & Me.txt_SongArtNameQry.Value & "*'"



Forms!frm_SongSearch.Repaint

End Sub
0
 
CSerpentAuthor Commented:
That worked Capricorn, thanks :) 1 down, 1 to go!
0
 
MINDSUPERBCommented:
@Cap

When my son see your profile, he aks me about MIT. He will be 4th year HS next year. Can I shoot you an email?

Thanks,
Ed
0
 
Rey Obrero (Capricorn1)Commented:
what is the recordsource of the form that creates the album?
you may just use table tbl_Albums as record source of the form
0
 
Rey Obrero (Capricorn1)Commented:
Ed, sure..
0
 
CSerpentAuthor Commented:
it uses tbl_Artists for calling the artist info, and tbl_Albums for the album info.
0
 
CSerpentAuthor Commented:
Was that sufficient capricorn, or do you need more info?
0
 
Rey Obrero (Capricorn1)Commented:
you did not answer the question i posted at http:#a34239302
0
 
CSerpentAuthor Commented:
Sorry missed it - yes the source is tbl_Albums.

I've ended up posting the DB on another Q where I've had issues if you want to look... http://filedb.experts-exchange.com/incoming/2010/11_w49/375011/music.mdb

0
 
Rey Obrero (Capricorn1)Commented:
CSerpent,
you are in good hands with LSM, so just follow his advise..

you can close this thread now.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 12
  • 10
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now