if an access database has 3 fields... Name, Required & On Hand.. so, if say i selected "Paper Goods" from combo1.. the NAME gets select.. but i have a lblRequired and lblOnHand.. I want those to get the "REQUIRED" AND "ON Hand" numbers for that record entry
how do you use the:
cmd.CommandText = "Select Name from Bathroom order by Name" 'at the moment i can only get all the NAMES into the combox.. but when i select the name - i want the other 2 pieces of info to show in the 2 labels
Sub test()
dbConnect
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cnn
cmd.CommandText = "Select Name from Bathroom order by Name" 'this is where i'm stuck
Set rs = cmd.Execute
Do While Not rs.EOF
Combo1.AddItem (rs!Name)
rs.MoveNext
Loop
Combo1.ListIndex = 0 'k, so it adds the names to the combobox..
rs.Close
cnn.Close
Label2.Caption = "Pantry / Bathroom" 'NEEDS TO BE EDITED
Combo1.Text = "-- SELECT --"
Frame1.Visible = "True"
Command2.Visible = "False" 'EDIT button
'label1
'label2
End Sub
2 ways
1) Add the ID to your first query, and store it in ItemData of the Combobox-Item. After clicking on a Combobox-Entry you just fire off a second query, grabbing those fields corresponding to that ID (which is in ItemData)
2) Select all 3 Fields in your first query from the get-go, and add them to an array. After clicking on a Combobox-Item grab the other values from the array using the ItemIndex as the Array-Index
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
Please change your field named "Name" in your database to something else...many times 'Name' is a reserved codeword....just to be safe!
If "On Hand" is a field name in your db, you'll need to surround it with brackets...like this:
Select itemName, required, [On Hand] from bathroom order by itemName
As far are putting the records into an Array, something like this
Code:
Dim sArray() as String '(you may have to REDIM)
Do While Not rs.EOF
sArray(0) = rs!itemName
sArray(1) = rs!required
sArray(2) = rs![On Hand]
rs.movenext
loop
As far are putting the records into an Array, something like this
Code:
Dim sArray() as String '(you may have to REDIM)
Do While Not rs.EOF
sArray(0) = rs!itemName
sArray(1) = rs!required
sArray(2) = rs![On Hand]
rs.movenext
loop
Errr....not exactly, Sam.
It should be a 2D-Array.
Kinda like (Aircode)
Code:
Dim sArray() As String
Dim i As Long
Redim (0 To RS.RecordCount-1, 0 To RS.Fields.Count-1)
For i=0 To UBound(sArray,1)
sArray(i,0)=RS!itemName
sArray(i,1)=RS!required
sArray(i,2)=RS!OnHand
Next
Be careful with Datatypes.
An alternative might be an 1D-Array of an UDT, containing the correct Types
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
Private Sub List1_Click()
Combo1.Clear
roomName = Trim(List1.Text)
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cnn
cmd.CommandText = "select itemName from " & roomName & " Order by itemName"
Set rs = cmd.Execute
If rs.RecordCount = 0 Then Exit Sub
Do While Not rs.EOF
Combo1.AddItem (rs!itemname)
rs.MoveNext
Loop
Combo1.ListIndex = 0
Combo1.Visible = True
Label1(1).Visible = True
Label1(2).Visible = True
lblRequired.Visible = True
lblOnHand.Visible = True
End Sub
To add a record, you will be using an "Insert" query....look up examples of this.
Make an attempt and post your code.
Deleting records are very simple...use a "Delete" query. Look for examples.
To Edit, you will want to use a Select query (like already demonstrated), put the result into a text box, and then use an "Update" query to put any changed name back into the database.
Take one of these challenges at a time...post each attempt with 'errors' if it fails.
To add a record, you will be using an "Insert" query....look up examples of this.
Make an attempt and post your code.
Deleting records are very simple...use a "Delete" query. Look for examples.
To Edit, you will want to use a Select query (like already demonstrated), put the result into a text box, and then use an "Update" query to put any changed name back into the database.
Take one of these challenges at a time...post each attempt with 'errors' if it fails.
Sammi
everything i search for adding an entry is either vbNET or vba or not in english... but then again, i only search youtube vids..
Last edited by BlakeSheldon; Aug 31st, 2024 at 01:17 PM.
Do you even know HOW to initialize your imagelist?
apparently not.. but i did point out the imagelist1 in the listview1 - if that isn't it then no i don't know cause it's not working and i'm not understanding why
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
OMG sorry geesh, don't get old! Just put any images and name them what you want. I'm not where I can get those files at the moment. I got I'll check my browsing history for where I got them.
Edit: on my phone, the swipe keyboard giving me fits.
I found the images on my PC. Look for arcade.jpg, I'll look on my laptop in the morning.
Sorry, sometimes I amaze myself with how dumb I can be.
Last edited by jdelano; Sep 4th, 2024 at 01:24 PM.
yeah, i can get the form and images to work from the temp folder in C folder.. but not an imagelist. but this will do, just gotta change the c\temp to app.path & "images" or something along those lines.
even then, i still to change that a little more because the form menus have sub menus.. so if say i click on "vegetables" and then say i click a sub menu.. say "peppers", then the imagelist would show the pepper images. if say i clicked con cucumbers, then the imagelist would show the images of cucumbers.
and then there is the count thing.. one thing at a time i guess
Last edited by BlakeSheldon; Sep 4th, 2024 at 01:38 PM.
i may have a new twist on this.. i'm working on a new form.. tho i do want to keep this form because it has it's uses.. but not for seeds.. same concept tho.. we'll talk.. still designing atm.. very similar, just opposite/different... again, each one has it's purpose.. i can explain the differences later if needed
Last edited by BlakeSheldon; Sep 4th, 2024 at 05:52 PM.
I see you have a bunch of flags in the imagelist, you can preload them like you have or keep using the SetupImagelist I switched it to use the images folder and also added code to use the temp subfolder to look for the text files.
The code reads whatever jpgs that are in that folder to display the image and simply uses the filename (before .jpg) as the key, the key is used to open an associated text file, which is just the jpg filename with a .txt, to get the data.
I did this simply to show how to load the image list and display a bit of something when it was clicked. You can use an alternative method to save and display your data.
Code:
Dim imagesFilePath As String
Private Sub Form_Load()
' set the images path to a subfolder of where the app is
imagesFilePath = App.Path & "\images\"
SetupImageList
FillListView
ListView1_Click ' select the first item in the list
End Sub
Private Sub SetupImageList()
' find the JPGs in the folder and add them to the imagelist for the listview
Dim jpgFiles As String
Dim jpgFileName As String
jpgFiles = Dir(imagesFilePath & "*.jpg")
ImageList1.ListImages.Clear
On Error Resume Next
While jpgFiles > vbNullString
' extract the filename to the left of the period
jpgFileName = Left(jpgFiles, InStr(jpgFiles, ".") - 1)
' add the image to the imagelist control
ImageList1.ListImages.Add , jpgFileName, LoadPicture(imagesFilePath & jpgFiles)
jpgFiles = Dir
Wend
End Sub
Private Sub FillListView()
' assign the image to the filename
Dim jpgFiles As String
Dim jpgFileName As String
jpgFiles = Dir(imagesFilePath & "*.jpg")
ListView1.ListItems.Clear
Set ListView1.Icons = ImageList1
On Error Resume Next
While jpgFiles > vbNullString
' extract the filename to the left of the period
jpgFileName = Left(jpgFiles, InStr(jpgFiles, ".") - 1)
' add it to the listview with its assigned image
ListView1.ListItems.Add , , jpgFileName, jpgFileName
If Err <> 0 Then
MsgBox jpgFileName & " does not have an associated image", vbInformation, "Skipping"
End If
jpgFiles = Dir
Wend
End Sub
Private Sub ListView1_Click()
Dim fName As String
Dim fso As Scripting.FileSystemObject
Dim fl As TextStream
Dim lineData() As String
Set fso = New Scripting.FileSystemObject
'fName = imagesFilePath & ListView1.SelectedItem.Text & ".txt"
' use the temp subfolder to look for the associated text file
fName = App.Path & "\Temp\" & ListView1.SelectedItem.Text & ".txt"
If fso.FileExists(fName) Then
' there is a file to read
Set fl = fso.OpenTextFile(fName)
' read the file, match the left portion of :
' to write to the textbox of the same name
Do Until fl.AtEndOfStream
lineData = Split(fl.ReadLine, ":")
Me.Controls("txt" & lineData(0)).Text = lineData(1)
Loop
fl.Close
Set fl = Nothing
Else
MsgBox "There isn't an associated information file (" & fName & ") for the selected item"
End If
Set fso = Nothing
End Sub
k, so i added an imagelist with progress bar images to the form.. however, i do not know how to code the percentage to work with what is required and what's on hand..
k, so..
if 0 OR 1 on hand is less than the qty required then show imagelist 1 picture
if 2 to 4 then show imagelist 2 picture
if half on hand vs what is required show imagelist 3 picture
if whatever to whatever on hand vs required show imagelist 4 picture
if on hand is equal to or great than required then show imagelist 5 picture..
something like that - i hope that makes sense..
i know it will be a select case.. but i only did this to test an IF statement which i knew would work.. but it's NOT percentage code.. so i couldn't try 1/2 percentage or any other percentage..
Code:
If lblOnHand = lblRequired Then
Image1.Picture = ImageList1.ListImages(5).Picture
End If
Last edited by BlakeSheldon; Sep 6th, 2024 at 08:10 PM.
The problem with hard coding values like "If it is 2 to 4 on hand then show picture 2" is that, what if the item on hand is something where your required number of them is only 4 to begin with?
The better option, assuming that the images are in proper numerical order in the ImageList is something like this (freehanded, not tested, and not guaranteed to work perfectly for you without some potentially minor changes):
Code:
If cint(lblOnHand.Caption) < cint(lblRequired.Caption) Then
Image1.Picture = ImageList1.ListImages(int(5*cint(lblOnHand.Caption) / cint(lblRequired.Caption))).Picture
Else
Image1.Picture = ImageList1.ListImages(5).Picture
End If
well if it's 4 & 4 as required and on hand then it would show image 5 a full bar. so it would have to be like 2 or 3 to show image 4 as example for talk sake i know that is NOT a percentage.. i'm just looking at limit between req and on hand.. but i can try yer idea.. like i said, i do not know how to do percentages between REQ and ONHAND.. there has to be a limit for each image..
100%, 75%, 50%, 25% & then anything under 25% - that would be the 5 images
Last edited by BlakeSheldon; Sep 6th, 2024 at 08:33 PM.
multiple record info from access DB
Reply With Quote
Originally Posted by SamOscarBrown
