Solved

A couple of VBA issues

Posted on 2010-11-30
27
256 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
 
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

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

you can close this thread now.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
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…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

856 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