multiple record info from access DB

[SamOscarBrown]

As OB1 stated...might I make a suggestion? Instead of, say, 40, in one of your lines, use 39.9999. If THAT ain't 'close enough', then use the Is Operator.

[BlakeSheldon]

i don't mind the suggestion honestly.. but atm it really is working just fine. all i wanted was a 2 digit number and nothing after it.. no decimals or extra numbers. well in the case of 100 or more then yes a 3 digit number as it is doing.

[SamOscarBrown]

[BlakeSheldon]

so moving forward to the next step.. is now UPDATING & DELETING an entry.. check out the updated project.. i really like how it's coming along.. a bit of will be coded into the module.

[BlakeSheldon]

question... why is it not showing the "&" symbol but showing an underline score in the name? when i clearly stated exact text in combo1..

Form1.Label2.Caption = "What are the new required and on hand quantities for " & Form1.Combo1.Text & "?"

[SamOscarBrown]

Use &&

[techgnome]

The longer answer to what's going on, when VB sees & in a string when setting it to a label, it thinks you want an Accelerator Key associated with it. An accellerator is how short cut key are associated with Buttons, LAbels, Menus... For example in the File menu, the caption is "&File" ... that is what lets you use Alt+F to access it. So if you want an actual & in the caption, you "escape" it with another one: &&


-tg

[BlakeSheldon]

updated... now it's ready to get coded for EDIT, DELETE a selected item.. check it out. i like it. the coding for this will go into the module... so that's something to look at.

oh and btw, i had to delete all former attachments so i could have room again to upload that's why you don't see any former images and zips.. shaggy told me how to make room to upload

[BlakeSheldon]

and yes, i did think of putting the second image stuff in a frame, but i didn't see a real reason for it. oh and i'm gonna make the txt's numeric only.. working on that now. i'm really liking how this is coming along so far..

ty everyone for yer help

[BlakeSheldon]

an old app i made years ago had this to accept only letters, but i'm looking for only numbers now for this instance:

If Not ((KeyAscii >= 97 And KeyAscii <= 122) Or _
      (KeyAscii >= 65 And KeyAscii <= 90) Or (KeyAscii = 8)) Then
 MsgBox ("Please enter letters only!"), vbInformation
 KeyAscii = 0
End If

[BlakeSheldon]

anyone with input?

[SamOscarBrown]

Look at that code you were given about letters only. You should be able to create one for numbers only.

to find the values you need, set form keyview to true and debug the ascii values when you hit the number keys. Then you can easily see how to make your text box allow only numbers (0 through 9) . OR, ask AI to write it for you. Very straight forward.

[SamOscarBrown]

Another suggestion-start a New thread!!!! In your first post you can include your latest project. But this thread has gone down too many rabbit holes. Title no longer reflects your current situation/questions

[BlakeSheldon]

Look at that code you were given about letters only. You should be able to create one for numbers only.

to find the values you need, set form keyview to true and debug the ascii values when you hit the number keys. Then you can easily see how to make your text box allow only numbers (0 through 9) . OR, ask AI to write it for you. Very straight forward.

i never said i couldn't do it.. and i don't need an AI to do it for me.

[BlakeSheldon]

Another suggestion-start a New thread!!!! In your first post you can include your latest project. But this thread has gone down too many rabbit holes. Title no longer reflects your current situation/questions

yes, it does because it still hasn't gotten to the point of EDITING and DELETING an entry.. yes, we do have ADD entries but not the final 2 parts.. so it's still within the title. after those 2 are completed - (because we are still dealing with multiple record info).. then i can start a new thread.

unless you want to help speed it up with EDIT and DELETE entries so i can start a new thread - til then i'll continue with this thread as it is still within the title limits. once those 2 things are complete then this thread will reflect the title which makes me move onto a new thread. trust me, i'd like to move on.. i have other things to do with this project.. but i have to go in order.

[BlakeSheldon]

Look at that code you were given about letters only. You should be able to create one for numbers only.

to find the values you need, set form keyview to true and debug the ascii values when you hit the number keys. Then you can easily see how to make your text box allow only numbers (0 through 9) . OR, ask AI to write it for you. Very straight forward.

that part is done.. now it's just a matter of EDITING and DELETING entries and then i can move on to a new thread.

Private Sub txtEditedOnHand_keypres(keyascii As Integer)
Dim Keychar As String
If keyascii > 31 Then
    Keychar = Chr(keyascii)
    If Not IsNumeric(Keychar) Then
        keyascii = 0
    End If
    End If
End Sub

Private Sub txtEditedRequired_keypress(keyascii As Integer)
Dim Keychar As String
If keyascii > 31 Then
    Keychar = Chr(keyascii)
    If Not IsNumeric(Keychar) Then
        keyascii = 0
    End If
    End If
End Sub

[BlakeSheldon]

thinking back to what i said in #89.. i am reconsidering it.. not sure yet tho cause that part is already hard coded in..

[BlakeSheldon]

@jdelano.. let me know which way to go with this as mentioned via PM's.. 2 forms OR 1 form with frames cause you coded in the "add item" to only what's available.. at this point i say leave the form alone and just create a new form as i have shown.. less headache to re-do it no?

[BlakeSheldon]

bump

[jdelano]

Hey, I made a few modifications to the project to make the code and component names more apparent.
I added code to the Add New Pantry form to add the new pantry table and initial item.

For others viewing this thread, the new code for creating / saving a new pantry

Private Sub btnSaveNewPantry_Click()

    ' has the user provided the pantry name
    If Len(Trim(txtNewPantryName.Text)) = 0 Then
        MsgBox "The pantry name is required", vbCritical, "Missing required data"
        txtNewPantryName.SetFocus
        Exit Sub
    
    ElseIf Len(Trim(txtNewPantryItem.Text)) = 0 Then
        MsgBox "The pantry item name is required", vbCritical, "Missing required data"
        txtNewPantryItem.SetFocus
        Exit Sub
    
    End If

    Dim listItemIndex As Integer
    Dim pantryNameExists As Boolean
    
    pantryNameExists = False  ' default the existance flag to false
    
    ' set this flag on the main form to prevent the listbox click event firing
    ' when it doesn't need to
    frmMain.newPantryFormLoopingList = True
    
    ' check to see if the pantry is a duplicate
    ' loop the pantry names on the main form (frmMain) and compare existing names
    listItemIndex = 0
    Do While Not pantryNameExists And listItemIndex <= frmMain.lbPantryNames.ListCount - 1
        
        frmMain.lbPantryNames.ListIndex = listItemIndex
        pantryNameExists = UCase(frmMain.lbPantryNames.Text) = UCase(txtNewPantryName.Text)
        listItemIndex = listItemIndex + 1
        
    Loop
    
    ' as this code is done checking the listbox items against the pantry name
    ' on this form, the flag can be turned off
    frmMain.newPantryFormLoopingList = False
    
    If pantryNameExists Then
        ' can't continue due to duplicate pantry name
        MsgBox "The pantry name already exists", vbCritical, "Duplicate Pantry Given"
        txtNewPantryName.SetFocus
        Exit Sub
    
    End If
    
    ' getting here means the new pantry can be created
    On Error Resume Next
    cnn.Execute "CREATE TABLE " & txtNewPantryName.Text & " (ID COUNTER, ItemName TEXT, numRequired NUMBER, numOnHand NUMBER);"
    
    If err <> 0 Then
        ' there was an error creating the table for the new pantry
        MsgBox "Error creating table '" & txtNewPantryName.Text & "'" & vbCrLf & "Error: " & Error(err), vbCritical, "Create Pantry"
        Exit Sub
    End If
    
    ' insert the record for the pantry item entered
    cnn.Execute "Insert into " & txtNewPantryName.Text & " (ItemName, numRequired, numOnHand) Values ('" & _
        txtNewPantryItem.Text & "'," & IIf(Len(Trim(Me.txtRequired.Text)) = 0, "0", Me.txtRequired.Text) & _
        "," & IIf(Len(Trim(txtOnHand.Text)) = 0, "0", txtOnHand.Text) & ")"
    
    If err <> 0 Then
        ' there was an error creating the table for the new pantry
        MsgBox "Error saving item " & txtNewPantryItem.Text & vbCrLf & "Error: " & Error(err), vbCritical, "Save Pantry Item"
        Exit Sub
    End If
    
    ' when the code gets here, then the new pantry and initial pantry item have been saved
    ' fill the listbox with all available pantry names from the database
    frmMain.LoadPantryNames
    
    DoEvents
    
    ' close this form now that the pantry and initial item has been saved
    Unload Me
    
End Sub

On Form1 (now frmMain) added this flag to tell the code in the list1 (now lbPantryNames) not to run the click event

Public newPantryFormLoopingList As Boolean

The form loads and calls a new LoadPantryNames sub (Public so the new pantry form can execute it)

Private Sub Form_Load()

    dbConnection
    
    newPantryFormLoopingList = False
    LoadPantryNames
    
End Sub

Public Sub LoadPantryNames()

    ' fill the listbox with the names of the pantries
    ' this is public to allow the new pantry form to call it after creating a new pantry
    lbPantryNames.Clear
    Set rs = cnn.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "Table"))
    
    Do Until rs.EOF
        lbPantryNames.AddItem " " & rs!TABLE_NAME
        rs.MoveNext
    Loop
    
    ' close and release the recordset object
    rs.Close
    Set rs = Nothing

End Sub

I tried to add a new zip file, but the site said I was over my quota, here is a link to it on my OneDrive https://1drv.ms/u/s!AkG6_LvJpkR7j60e...jhXWg?e=4xlXMr

I may work on it a bit tomorrow.

EDIT: added a check of count of listitems to the Do While Loop and listIndex starting at 0
EDIT2: modifed a couple notes and reuploaded the project

[BlakeSheldon]

i did notice on "add pantry section form".. frmNewPantrySection.. the code had to be changed to the following for the program to run and work:

        For i = 0 To frmMain.lbPantryNames.ListCount - 1
            List1.AddItem " " & frmMain.lbPantryNames.List(i)
        
        Next i

i will also add some msgboxes as it will add an empty section and empty quantities and same with add item to a current section.. but after all this it's just the edit button part.. i want to be able to edit the quantities per item, but that will come later

[jdelano]

Indeed, you'll want to add detection of unwanted items.

I don't think I'll do much today. What you can look up is using an update query from VB6 to an access database to handle the, well updating a record.

[BlakeSheldon]

started a new topic just for a msgbox question since it was viewed multiple times but not answered

[BlakeSheldon]

ok, sorry to bring this post back up, but i have a serious issue here.. when creating a new pantry section OR editing a name/section in the DB i come across errors, well not so much as errors.. just it's NOT creating what i input to create.. like you can create a new pantry section IF you have a an underscore or something in between words.. like: "Canned_Goods" - creates a new table/section, but it doesn't create a new pantry section if you have a space in between words like: "Canned Goods". the form to be looking at is: frmNewPantrySection in the project.


frmNewPantrySection code:

Private Sub Command1_Click()
               
If txtCategory = "" Then
  MsgBox "You must select a pantry section!", vbCrtical, "Prepper Helper"
  List1.SetFocus
  Exit Sub
  Else
  If txtName.Text = "" Then
  MsgBox "You must enter an item name!", vbCrtical, "Prepper Helper"
  txtName.SetFocus
  Exit Sub
  Else
  If txtRequired = "0" Or txtRequired = "" Then
  MsgBox "You must enter a valid required amount which must be higher than 0!", vbCrtical, "Prepper Helper"
  txtRequired.Text = ""
  txtRequired.SetFocus
  Exit Sub
  Else
  If txtOnHand = "" Then
  MsgBox "You must enter a valid on hand amount!", vbCrtical, "Prepper Helper"
  txtOnHand.SetFocus
  Exit Sub
  End If
  End If
  End If
  End If
      
        
     'when the above requirements are met, then proceed ...
        
        ' add the data to the database
        Set cmd = New ADODB.Command
        Set cmd.ActiveConnection = cnn
        
        
        ' create an insert statement to save the data
        cmd.CommandText = "Insert Into " & txtCategory.Text & " (ItemName, numRequired, numOnHand) " & _
            "Values ('" & txtName.Text & "'," & txtRequired.Text & "," & txtOnHand.Text & ")"
        
        cmd.Execute
        

        'success
            Me.Hide 'hide the form while keeping the info to show the info in the msgbox because we don't want to keep the form visible with the msgbox on top of it
            MsgBox Trim(txtName.Text) & " has been added to " & Trim(txtCategory.Text) & "!", vbInformation, "Prepper Helper"
            Unload Me 'unload the hidden form after pressing ok in the msgbox as the form is not needed anymore
    
        
        Set cmd = Nothing

End Sub

Private Sub Form_Load()
                
        For i = 0 To frmMain.lbPantryNames.ListCount - 1
            List1.AddItem " " & frmMain.lbPantryNames.List(i)
        Next i
        
End Sub

Private Sub List1_Click()
    txtCategory.Text = List1.Text
    txtName.SetFocus
End Sub
Private Sub txtOnHand_keypres(keyascii As Integer)
'only allow numbers
Dim Keychar As String
If keyascii > 31 Then
    Keychar = Chr(keyascii)
    If Not IsNumeric(Keychar) Then
        keyascii = 0
    End If
    End If
End Sub

Private Sub txtRequired_keypress(keyascii As Integer)
'only allow numbers
Dim Keychar As String
If keyascii > 31 Then
    Keychar = Chr(keyascii)
    If Not IsNumeric(Keychar) Then
        keyascii = 0
    End If
    End If
End Sub
Private Sub txtName_keypress(keyascii As Integer)
'only allow letters,  spaces and backspace
    Select Case keyascii
        Case 8 ' backspace
        Case 65 To 90 'A-Z
        Case 97 To 122 'a-z
        Case 32 'blank space
        Case Else
            keyascii = 0
    End Select
End Sub

[BlakeSheldon]

and say i rename a table in the DB from "Paper_Goods" to "Paper Goods".. and then run the program, i get one of these errors: it's like it doesn't like or know how to accept a space between words for a new section/table to create it.. whatever you wanna call it

[OptionBase1]

Putting spaces in the name of a table or a column is generally frowned upon. However, it can be done if needed, and I believe the way to make it work is to enclose the name in []. This would need to be done in every query and every place where either a table name or a column/field name may have a space in it, which may be cumbersome.

To "fix" this for the code you posted, this should work:

cmd.CommandText = "Insert Into [" & txtCategory.Text & "] (ItemName, numRequired, numOnHand) " & _
            "Values ('" & txtName.Text & "'," & txtRequired.Text & "," & txtOnHand.Text & ")"

But as I said, this would need to be done everywhere else where you are referencing tables by name in other queries, so I will leave it up to you to figure out where those are and make the same fix.

[BlakeSheldon]

Putting spaces in the name of a table or a column is generally frowned upon

well keep in mind.. the end user is creating a new pantry section/table.. so they would not know that..

[OptionBase1]

well keep in mind.. the end user is creating a new pantry section/table.. so they would not know that..

True, but your program could prevent the user from ever entering a space in the textboxes where table or field names are input, which makes the issue moot.

[BlakeSheldon]

True, but your program could prevent the user from ever entering a space in the textboxes where table or field names are input, which makes the issue moot.

true, agreed.. but if they wanted to say put "The Basement", i think they should have the right to have spaces in between words.. and not force them to making it 1 lined text like "TheBasement" or "thebasement" or The_Basement" or "the_basement". so why would i prevent them from doing so? it's how THEY want to create their sections afterall no? it's THEIR choice on how THEY want to name things out.. not mine.

[OptionBase1]

Well, I gave you code that should fix it in one place in your program. Not sure if you saw that or just ignored it and decided that you just want to argue.

[OptionBase1]

And to reiterate what I said earlier:

Putting spaces in the name of a table or a column is generally frowned upon. However, it can be done if needed, and I believe the way to make it work is to enclose the name in []. This would need to be done in every query and every place where either a table name or a column/field name may have a space in it, which may be cumbersome.

To "fix" this for the code you posted, this should work:

cmd.CommandText = "Insert Into [" & txtCategory.Text & "] (ItemName, numRequired, numOnHand) " & _
            "Values ('" & txtName.Text & "'," & txtRequired.Text & "," & txtOnHand.Text & ")"

But as I said, this would need to be done everywhere else where you are referencing tables by name in other queries, so I will leave it up to you to figure out where those are and make the same fix.

Translation: Don't come back after making this one single change in the one line of code I posted and say it isn't working. You must surround table names with [] in each and every query in your code yourself.

[BlakeSheldon]

i saw the code, doesn't mean i have tested it out yet.. no need to come back at me back to back with it and explode it! my god, can i not have a conversation with you without you stirring things up? you need to chill out dude!

and no, i don't want to argue with you.. you just make me want to when you say or do certain things like you just did!