Access help

Smiley Man · 27 Apr 2007 at 12:17

hi guys

i'm having a bit of trouble with a database ive got in access here

i've got a from which has 2 values:

CD_ID
Vinyl_ID

and a button which should load a vinyl information form if the Vinyl_ID has a value in it and the CD_ID value is null and vice versa

i have Remix_ID determining what information to show on the vinyl or cd info form although thats irrelevant

what syntax do i need to use in the visual basic expression builder? i have this but it doesnt work

Code:

Private Sub Command20_Click()

    Dim stDocName As String
    Dim stLinkCriteria As String
    
CD_ID.SetFocus
If CD_ID.Text = Null Then

    stDocName = "frm_vinylinfo"
    
    stLinkCriteria = "[RemixID]=" & Me![RemixID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Else

    stDocName = "frm_cdinfo"
    
    stLinkCriteria = "[RemixID]=" & Me![RemixID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    End If
    
End Sub

please help

Helium_Junkie · 27 Apr 2007 at 15:04

I thought this was supposed to have been done by now

Can you upload it again? I'm off at about 4 but if you can upload before that I'll try and take a look.
My e-mail is in trust, feel free to plague me on that over the weekend if you still need help.

Helium_Junkie · 27 Apr 2007 at 15:11

Wait a mo, I thought your CD and Vinyl tables were:

CD_ID
Wallet Page
Date

Viny_ID
Date

???

If thats the case then you dont pass the remix ID as your criteria, you pass the CD_ID or the Vinyl_ID.
This is the code you should need, note that you use "Value" instead of text, and <> (not equal to) "" (blank). This will mean that if your vinyl and cd id's are both blank you dont get an error trying to open a form.

Code:

Dim stDocName As String
    Dim stLinkCriteria As String
    
    CD_ID.SetFocus
    
    If CD_ID.Value <> "" Then

        stDocName = "CD Form"
        stLinkCriteria = "[CD_ID]=" & Me![CD_ID]
        DoCmd.OpenForm stDocName, , , stLinkCriteria
    Else
    
        Vinyl_ID.SetFocus
    
        If Vinyl_ID.Value <> "" Then
    
            stDocName = "Vinyl Form"
            stLinkCriteria = "[Vinyl_ID]=" & Me![Vinyl_ID]
            DoCmd.OpenForm stDocName, , , stLinkCriteria
        End If
    
    End If

edit: wow vba is restrictive

Smiley Man · 27 Apr 2007 at 16:43

ok ive tried that code and the button doesnt work at all now

i will have a fiddle see what i can do

i added you to msn i hope yo dont mind

Smiley Man · 27 Apr 2007 at 16:56

got it now

gah its problem after problem with this thing, why cant access just do what you want it to do?

Helium_Junkie · 28 Apr 2007 at 14:28

Smiley Man said:
ok ive tried that code and the button doesnt work at all now

i will have a fiddle see what i can do

i added you to msn i hope yo dont mind

That code would only do nothing if your record doesnt have any ID for cd or vinyl. Of both are null the button does nothing.

Sure thing with MSN, shout at me any time

Smiley Man · 30 Apr 2007 at 12:51

i need to write a fairly complex query (for me anyway) but havent got a clue, at college all they taught us is how to make a basic one in design view or even more basic ones in sql :mad:

Smiley Man · 30 Apr 2007 at 21:19

Code:

    stDocName = "tbl_Remix Subform"
    stLinkCriteria = "[Key]=" & "'" & Me![Key] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    
End If

I've got this code so far to show tunes in the same key as the one you're viewing details on but as well as showing tunes in the same key i want it to show ones in compatible keys

So at the moment if you have a track in Am and run the code it only shows tracks in Am, but i want it to show Am, Dm, Em and C

to do this for all the keys will use loads of ifs i think but i should be able to do that, its just the code to show more than one key in the opened form

please help

Mirage · 1 May 2007 at 06:52

First of all ask yourself, how does the database know that these are all the same key?

Smiley Man · 1 May 2007 at 07:57

know that what are the same key? at the moment it shows tunes in the same key as the one you're currently viewing information on

Smiley Man · 1 May 2007 at 12:12

i've got it now

but access has just decided to throw up a random "there is not enough memory" error, and wont even let me delete any code, i've tried it on more than one computer

ive got old backups but a program like access still shouldnt just decide to screw up hours of work whenever it feels like it

:mad:

Mirage · 1 May 2007 at 12:17

Have you tried loading while holding shift to stop it running code?

Smiley Man · 1 May 2007 at 13:01

Mirage said:
Have you tried loading while holding shift to stop it running code?

when loading access or the individual form?

Helium_Junkie · 2 May 2007 at 14:52

An out of memory implies you've added an infinite loop somehow.

To do what you want it to do you're better off adding a table with key relations in, and having your songs using a key from the key table as a foreign key.

Key Table
Key (String, Primary Key)

Key Relations Table
Relation ID (Autonumber, Primary Key)
Key (String, Foreign Key)
Related Key (String, Foreign Key)

Then set up every relation in that table, so for Am you'd input:

ID: 1
Key: Am
Related Key: Dm

ID: 2
Key: Am
Related Key: Em

ID: 3
Key: Am
Related Key: C

And so on, for all the relations possible.

Then do something relatively clever in the query builder and (I think) the inner-join function.
Having options like that in code is very bad database form. It really should be in a table.