Solved

A couple of VBA issues

Posted on 2010-11-30
27
259 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

735 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