Newb Question on linking tables

[Frothingslosh]

RL, I'd actually considered that approach earlier, but didn't go with it mainly due to wanting to keep it simple, since Trachr is new to this. Personally, I'd normally put the publisher in the series table, but that's just because it's incredibly rare for a series to change publishers. (Sandman jumping from DC to Vertigo in the late 80's is the only one that comes to mind, but I'm not a comics guy). Other than that, I agree with you.

 

[Trachr]

I am trying to get it all right before I get too far into my forms and reports... I only have like 4 forms done so nothing is very hard to change at the moment.... thats why I stopped all progress on my forms and an seriously thinking out what I want in my tables before I go any further...

If I am going to give this to ppl even for free I want it to look at professional as humanly possible which is why Im trying to think this through from all angles.

I know I wont get it perfect, but theres no harm in trying to do the best I can with it

And even for myself, Im still trying to figure oout if I can get all the features I want in it.... for instance atm I do have a VERY rudimentary database that just keeps track of what comics I have, I dont use forms I just enter them directly into a table... hell I have to since I do it in excel.

Anyway I also have a 2nd excel form that I enter every few weeks that I have a list of various comics series that have a list of comics Im missing that way I can print it off if I ever go to a store so I dont have to remember everything...horrid memory.....thats why I wanted the wish list

ANyway Im sitting back now and trying to think of anything else that will make my life easier later lol

Speaking of which... my idea for setting it up so I could know how many issues were in each volume for any given series, would that work? Because that would be a handy feature... especially if you could compare that against your comic list so youd know if you were missing any... adding to the wish list on a comic by comic basis is good but it would be easy to miss one here and there if you dont know how many comics there were in a given volume of a series.

 

[RainLover]

If I am going to give this to ppl even for free I want it to look at professional as humanly possible which is why Im trying to think this through from all angles.

I know I wont get it perfect, but theres no harm in trying to do the best I can with it

Very nice intentions. I have been there and done that and I charged $500.00 per copy. I did find that people were over demanding in what they got. They wanted 24 hour support for a start.

Up to you, just think it through first.

 

[RainLover]

Speaking of which... my idea for setting it up so I could know how many issues were in each volume for any given series, would that work?

I am not 100% clear as to which way you want to do this. Please explain.

 

[Trachr]

I would add a new tblsomethingoranother and that would have a few fields in it, it would have SeriesID_FK linked to SeriesID_PK, then it would have Volume, then a field like start issue, and final issue

Theory being that way I could easily know how many issues were in a volume of a series... then (if this is possible) I could run a check vs all the comics I have to see what Im missing

Obviously Id come up with a better table name

 

[Trachr]

You know what may just be easier... If I treat each Volume as a separate series, then in tblseries I could add in a issuestart and a issueend or something along those lines would probably make everything a lot easier.... would make it hard to know total number of volumes for each series but thats a minor issue I would just have to be sure to add a series for each volume when I looked it up.

 

[Frothingslosh]

You know what may just be easier... If I treat each Volume as a separate series, then in tblseries I could add in a issuestart and a issueend or something along those lines would probably make everything a lot easier.... would make it hard to know total number of volumes for each series but thats a minor issue I would just have to be sure to add a series for each volume when I looked it up.

That would probably be the best approach in the long run.

 

[Trachr]

hmmm Guess I can get started on my forms again... expect more questions soon lol

 

[Trachr]

Sorry for no updates... got sidetracked with a kidney stone... getting it removed monday... lucky me lol

 

[Frothingslosh]

Sounds painful...

 

[RainLover]

Storing calculated fields is not good. These things should be recalculated when required if you can.

IssueStart and IssueEnd may be examples of this.

Obviously Id come up with a better table name

You should always present your best when asking for help. Make sure your Naming is correct your code tidy etc.

Good luck trying to get that Stone to roll out of there.

 

[Trachr]

Well I think Im recovered enough to be off my pain pills mostly, which means im finally in my right mind again.

So what Im working on is simple I think... I have a code where Im trying to add a value with vba ... its to a table that only has 1 field that isint an autonumber...Im doing this for both the experience and so I dont have to make a whole new form just for 1 simple entry.

This Problem may be more complex then some of my others...

In my second ElseIf statement I was going to add a duplicate checker so it will stop a save if there already is a record in the table for that publisher.
I was considering using a Case statement somehow but still am unsure on how.

So any advice would be helpful.

Heres what I have so far:

the table is tblPublishers
the fields in that table are: PublisherID_PK which is an autonumber, and PublisherName which is short text

Private Sub publistAdd_Click()
    Dim Publisher_Name As String
    Publisher_Name = InputBox("Enter Publisher Name", "Add Publisher", "")
    If (StrPtr(Publisher_Name) = 0) Then
        Exit Sub
        ElseIf (Publisher_Name = "") Then
            MsgBox "Invalid Entry"
        ElseIf (Publisher_Name = "Duplicate Checker") Then
            MsgBox "Duplicate Entry"
        Else
            DoCmd.SetWarnings False
            DoCmd.RunSQL "INSERT INTO tblPublishers ([PublisherName]) Values ('" & Publisher_Name & "')"
            Me.publistList.Requery
    End If
End Sub

Obviously if you see any major flaws in my coding that I don't know about please let me know

Thanks

 

[Trachr]

Ok updated this post... Now I just need some help with making my code look right... unless you see some major flaws.

Question about Scripting however... what is more efficient, to set variables only certain things if they are applicable, ie certain criteria are met... or to just set them at the start on the chance that said criteria will be met.... My 2nd bit of code is a good example...I have all my DIMs set after an Else statement... is that bad code? or if its good to nest them in as far as possible I can go even further then that.

anywhere here goes

Private Sub publistAdd_Click()
    Dim Publisher_Name As String
    Publisher_Name = InputBox("Enter Publisher Name", "Add Publisher", "")
    If (StrPtr(Publisher_Name) = 0) Then
        Exit Sub
    ElseIf (Publisher_Name = "") Then
            MsgBox "Invalid Entry"
    Else
        If IsNull(DLookup("[PublisherName]", "[tblPublishers]", "[PublisherName]='" & Publisher_Name & "'")) Then
            DoCmd.SetWarnings False
            DoCmd.RunSQL "INSERT INTO tblPublishers ([PublisherName]) Values ('" & Publisher_Name & "')"
            Me.publistList.Requery
        Else
            MsgBox "Duplicate Entry, Aborting"
        End If
    End If
End Sub

2nd code is basically the same as the above only now I want to be able to edit entries already in the table instead of add... so I had to make a few key changes.... I also wanted to make it so the user could change the caps of a publisher if they wanted to.

Private Sub publistEdit_Click()
    If IsNull(Me.publistList) Then
        MsgBox "Please Select a Publisher"
    Else
        Dim Publisher_Name_Original As String
        Dim Publisher_Name_New As String
        Dim Publisher_Ident As String
        Dim Publisher_CapsCheck As String
        Publisher_Name_Original = Me.publistList
        Publisher_Name_New = InputBox("Enter Corrected Publisher Name", "Add Publisher", "" & Publisher_Name_Original & "")
        Publisher_CapsCheck = StrComp("'" & Publisher_Name_New & "'", "'" & Publisher_Name_Original & "'", vbBinaryCompare)
        Publisher_Ident = DLookup("[PublisherID_PK]", "[tblPublishers]", "[PublisherName]='" & Publisher_Name_Original & "'")
        Select Case Publisher_CapsCheck
            Case 0
                MsgBox "Publisher Name Unchanged"
            Case 1, 1
                DoCmd.RunSQL "Update tblPublishers SET PublisherName='" & Publisher_Name_New & "' WHERE PublisherID_PK=" & Publisher_Ident & ""
                Me.publistList.Requery
            Case Else
                If IsNull(DLookup("[PublisherName]", "[tblPublishers]", "[PublisherName]='" & Publisher_Name_New & "'")) Then
                    DoCmd.RunSQL "Update tblPublishers SET PublisherName='" & Publisher_Name_New & "' WHERE PublisherID_PK=" & Publisher_Ident & ""
                    Me.publistList.Requery
                Else
                    MsgBox "Duplicate Entry, Aborting"
                End If
        End Select
    End If
End Sub

Thanks

 

[Trachr]

On the 2nd code above.. I suppose I could remove Case 0 ... its not really needed with the else in case else....

What really bugs me though is I couldnt figure a way to only do the docmd.runsql line once... had to duplicated it since I also needed to check for duplicate entries in the whole table...but oh well thats not a huge issue compared to my first draft of this code... talk about if/else nightmare zone lol

Also I need to start commenting my code so I remember this in the future.... those are my only gripes anyway... but Im a lot newer at this then everyone else

 

[RainLover]

I would rather not comment without know that what I wrote was correct.
This could only be done with a properly working copy.
Can you supply a copy of the Database.

A couple of generalised hints.

You do need some error trapping. Once you get the code started you need to be able to find what went wrong if something does indeed go wrong.

Sample error trap.

Private Sub CmdNext_Click()

On Error GoTo Err_ErrorTag

    DoCmd.GoToRecord , , acNext

Exit_ErrorTag:
    Exit Sub

Err_ErrorTag:
    MsgBox "Error: " & Err.Number & Chr(32) & Err.Description, _
        vbInformation + vbOKOnly, " Error in FrmAcq CmdNext "
    Resume Exit_ErrorTag
    
End Sub

With Do.Cmd. SetWarnings this needs to be restarted if it is closed like you have done. For Example if you started an Hour Glass it will stay on maybe until you either restart the program or reboot the computer.

There are better ways.

This will do for now until I get that copy.

 

[Trachr]

This version only has 1 form but its the form Im working on so that works... Im adding more as I go, I had like 4 in my original version but haven't got them all transferred over yet.

But luckily for this question we only need the 1 form lol

here ya go, had to compress it since its already bigger then the upload limit.

 

[RainLover]

You want your code to be tidy yet you send me :-

Private Sub publistExit_Click()

End Sub

Instead of using DoCmd.RunSQL try using

 CurrentDb.Execute strSQL, dbFailOnError

This will allow you to delete Set Warnings.

Every Page at the top as part of your declarations requires

Option Explicit.

Create some white space in your code so that it is very easy to look at. Space is very cheep.

That is something for you to do. I am going back to bed. It is 4:00 in Mid winter here.

 

[Trachr]

I was leaving the exit sub for now till I add more forms, so I can add some if statements to refresh whatever forms may need to be refreshed.... I cant enter anything into that sub just yet.

Thanks

 

[Trachr]

Ok so 2 questions, hopefully simple ones.... first off Ive started altering my code like you suggested and its going fine, I had to add another variable for some reason, Im not entirely sure why I NEED a Publisher_Edit_SQL but I couldnt get it to work without it for the life of me.... looks nicer with it anyway so I wont complain, it will allow me to troubleshoot the update line easier since now its in 1 location instead of 2.

Anyway First off my code for what Im talking about

Private Sub publistEdit_Click()
    If IsNull(Me.publistList) Then
        MsgBox "Please Select a Publisher"
    Else
        Dim Publisher_Name_Original As String
        Dim Publisher_Name_New As String
        Dim Publisher_Ident As String
        Dim Publisher_CapsCheck As String
        Dim Publisher_Edit_SQL As String
        Publisher_Name_Original = Me.publistList
        Publisher_Name_New = InputBox("Enter Corrected Publisher Name", "Add Publisher", "" & Publisher_Name_Original & "")
        Publisher_CapsCheck = StrComp("'" & Publisher_Name_New & "'", "'" & Publisher_Name_Original & "'", vbBinaryCompare)
        Publisher_Ident = DLookup("[PublisherID_PK]", "[tblPublishers]", "[PublisherName]='" & Publisher_Name_Original & "'")
        Publisher_Edit_SQL = "Update tblPublishers SET PublisherName='" & Publisher_Name_New & "' WHERE PublisherID_PK=" & Publisher_Ident & ""
        Select Case Publisher_CapsCheck
            Case 0
                MsgBox "Publisher Name Unchanged"
            Case 1, -1
                CurrentDb.Execute Publisher_Edit_SQL, dbFailOnError
                Me.publistList.Requery
            Case Else
                If IsNull(DLookup("[PublisherName]", "[tblPublishers]", "[PublisherName]='" & Publisher_Name_New & "'")) Then
                    CurrentDb.Execute Publisher_Edit_SQL, dbFailOnError
                    Me.publistList.Requery
                Else
                    MsgBox "Duplicate Entry, Aborting " & PublisherSQL & ""
                End If
        End Select
    End If
End Sub

ok now the 2 quirks im running into that Im not entirely sure why

1.) If I click edit on one of my Publishers then click cancel on the entry it dosent cancel, it in fact enters a null entry, Im sure I can figure a way around it but Im just curious as to why? Ill use another If statement to make sure that the sub exits in that instance but if there is an easier way please let me know

2.) Stored Variables or maybe the table not updating... If I try to edit the same publisher twice, the first value is the one that shows up in Publisher_Name_Original despite the fact that it shows up differently in my listbox.... This confuses me since the operation "should" change the variable each time I click the edit button so Im not entirely sure what is happening here.


Also in response to your comment about leaving more space, is that common practice? its actually easier for me to see things condensed for some reason, if it gets too spread out I get confused easier lol. But before posting my code Id be happy to add a few spaces.


*edit*

Heres the code I used to solve my first problem: Youll notice a new If/Elseif/Else set

Still confused by my 2nd problem... Ive determined it even shows the pervious value from me.publicList despite the fact Ive requeried it AND I can see the updated values on the form before I hit edit.

Private Sub publistEdit_Click()
    If IsNull(Me.publistList) Then
        MsgBox "Please Select a Publisher"
    Else
        Dim Publisher_Name_Original As String
        Dim Publisher_Name_New As String
        Dim Publisher_Ident As String
        Dim Publisher_CapsCheck As String
        Dim Publisher_Edit_SQL As String
        
        
        Publisher_Name_Original = Me.publistList
        Publisher_Name_New = InputBox("Enter Corrected Publisher Name", "Add Publisher", "" & Publisher_Name_Original & "")
        Publisher_CapsCheck = StrComp("'" & Publisher_Name_New & "'", "'" & Publisher_Name_Original & "'", vbBinaryCompare)
        Publisher_Ident = DLookup("[PublisherID_PK]", "[tblPublishers]", "[PublisherName]='" & Publisher_Name_Original & "'")
        Publisher_Edit_SQL = "Update tblPublishers SET PublisherName='" & Publisher_Name_New & "' WHERE PublisherID_PK=" & Publisher_Ident & ""
        
        
        If (StrPtr(Publisher_Name_New) = 0) Then
            Exit Sub
        ElseIf (Publisher_Name_New = "") Then
            MsgBox "Invalid Entry, Aborting"
        Else
            Select Case Publisher_CapsCheck
                Case 0
                    MsgBox "Publisher Name Unchanged"
                Case 1, -1
                    CurrentDb.Execute Publisher_Edit_SQL, dbFailOnError
                    Me.publistList.Requery
                Case Else
                    If IsNull(DLookup("[PublisherName]", "[tblPublishers]", "[PublisherName]='" & Publisher_Name_New & "'")) Then
                        CurrentDb.Execute Publisher_Edit_SQL, dbFailOnError
                        Me.publistList.Requery
                    Else
                        MsgBox "Duplicate Entry, Aborting " & PublisherSQL & ""
                    End If
            End Select
        End If
    End If
End Sub

 

[RainLover]

I was leaving the exit sub for now till I add more forms, so I can add some if statements to refresh whatever forms may need to be refreshed.... I cant enter anything into that sub just yet.

Thanks

Can you explain this a bit better.

I am not at all sure what point you were making.