Solved

A couple of VBA issues

Posted on 2010-11-30
27
252 Views
Last Modified: 2012-05-28
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
Comment
Question by:CSerpent
  • 12
  • 10
  • 5
27 Comments
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 34238436

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
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 34238447
It should mean as: Do check as well the record source of your form.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 34238507
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
 

Author Comment

by:CSerpent
ID: 34238597
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 34238606
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
 

Author Comment

by:CSerpent
ID: 34238670
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 34238705
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 34238717
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
 

Author Comment

by:CSerpent
ID: 34238748
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 34238803
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
 

Author Comment

by:CSerpent
ID: 34238823
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
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 34238889
Have a check the Field types of the ASIN field on both tables.

Ed
0
 

Author Comment

by:CSerpent
ID: 34238907
AutoNumber and Number respectively
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 34238994
Can you post the screenshot of the said query in design view? You may create a new query and use that statement.

Ed
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 34239000
<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
 

Author Comment

by:CSerpent
ID: 34239013
@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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 34239058
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
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 34239070
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
 

Author Comment

by:CSerpent
ID: 34239230
That worked Capricorn, thanks :) 1 down, 1 to go!
0
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 34239284
@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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 34239302
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 34239311
Ed, sure..
0
 

Author Comment

by:CSerpent
ID: 34239333
it uses tbl_Artists for calling the artist info, and tbl_Albums for the album info.
0
 

Author Comment

by:CSerpent
ID: 34243376
Was that sufficient capricorn, or do you need more info?
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 34244089
you did not answer the question i posted at http:#a34239302
0
 

Author Comment

by:CSerpent
ID: 34244116
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 34244698
CSerpent,
you are in good hands with LSM, so just follow his advise..

you can close this thread now.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

930 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now