Solved

A couple of VBA issues

Posted on 2010-11-30
27
249 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
Comment Utility

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
Comment Utility
It should mean as: Do check as well the record source of your form.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Have a check the Field types of the ASIN field on both tables.

Ed
0
 

Author Comment

by:CSerpent
Comment Utility
AutoNumber and Number respectively
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 19

Expert Comment

by:MINDSUPERB
Comment Utility
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
Comment Utility
<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
Comment Utility
@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
Comment Utility
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
Comment Utility
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
Comment Utility
That worked Capricorn, thanks :) 1 down, 1 to go!
0
 
LVL 19

Expert Comment

by:MINDSUPERB
Comment Utility
@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
Comment Utility
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
Comment Utility
Ed, sure..
0
 

Author Comment

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

Author Comment

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

Expert Comment

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

Author Comment

by:CSerpent
Comment Utility
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
Comment Utility
CSerpent,
you are in good hands with LSM, so just follow his advise..

you can close this thread now.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

772 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

14 Experts available now in Live!

Get 1:1 Help Now