Solved

Use form fields to run Query

Posted on 2011-03-03
11
1,202 Views
Last Modified: 2012-05-11
OK, I'm going to try my best to explain my situation.

I have Part Numbers and Product Images set-up in my database. I would like for someone to be able to input a part number into a field in a form and have the product image that matches that part number appear above it. I would like to have the images set-up in two rows of 16 to match a physical display that we have. Therefore, if somebody wants to change what's on the product sample display, they could type in the part numbers, have the images appear, then print the layout for a reference.

If you need anymore information from me, let me know. Thanks in advance.
0
Comment
Question by:Mike McBride
  • 6
  • 3
  • 2
11 Comments
 
LVL 3

Expert Comment

by:DockieBoy
ID: 35031680
Where and how exactly is the data stored, can you please provide the table structure, eg;

tblParts

PartId - AutoNumber, Pk
PartNumber
PartDescription
PartImage

Thanks
0
 
LVL 2

Expert Comment

by:bobHacker
ID: 35035929
Hello,

Just an approach you might want to try...

1. Set up a combo box control on the form
2. Create a query for the data you want
3. Make the Row Source Type = Table/Query
4. Make the Row Source the name of the query
5. limit to list = yes
6. set the column count of how may columns are
   returned from the query
7. Use the column widths to adjust what is shown in
   the combo box for the user and set List Width
   Column Widths setting example for showing 3
   columns = 0.75";2";0"
8. Tweak these combo box properties to get them
   the way you want them
9. In the combo box pick an appropriate event,
   like "On change"
   
   In the event routine add code that will
   populate the fields from the query rows example:

    'Based on selection populate the fields
    Me.txtVendorNumber = Me.cboVendor.Column(0)
    Me.txtVendorName = Me.cboVendor.Column(1)
    Me.txtAddress1 = Me.cboVendor.Column(2)
    Me.txtAddress2 = Me.cboVendor.Column(3)
    Me.txtCity = Me.cboVendor.Column(4)
 
    This will fill the fields on the form based
    on the selection made in the combo box

Hope this helps you get what you need.
B
0
 

Author Comment

by:Mike McBride
ID: 35036246
I have a table set-up with many pieces of information, way more than I need to use. The query will look up Part Numbers and Pictures from the table with the criteria that the company name is our company. I've got that part working fine. However, the images that I have in the table are linked .DIB files, not embedded images. I think that this might be the problem. When I try to use bobHacker's solution, it gives me an error that the object doesn't support this property or method (Error 438). I tried using a bound and an unbound object frame, and they each returned errors when trying to apply the OLE Object from the table. Any tips about applying that image to the field, or will I have to embed the images into the database?
0
 
LVL 2

Expert Comment

by:bobHacker
ID: 35036517
Hello mac

I have not worked with images but I would not store them in the database
for size conciderations.

I would store them in a folder and in a database table
you could use a key field # in a table that points to where
your pic is and load it on the form based on the key

imageID imagePath     imageName
1       "H:\images\"  partxxx.jpg
 
in the form
add a query / QDF (based on the selection from the combo box)
get imageID, imagePath, and ImageName

load the image object

Image1.Pic = strImagePath & strImageName)
 
(Image1.Pic = "H:\images\" & imageName)

Have not seen your err 438  

Hope this helps you get things going.

B
0
 

Author Comment

by:Mike McBride
ID: 35037626
I uploaded an image of how my form looks currently. This is how I would like the final to look.
Sample-Layout.jpg
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.

 
LVL 2

Expert Comment

by:bobHacker
ID: 35037741

Hi

Perhaps another expert could help you further?

thank you
B
0
 

Author Comment

by:Mike McBride
ID: 35039845
OK, I've made a bit of progress with this now. My form is still laid out as in my previous post. I have 32 text boxes (Text1 - Text32) and each text box has a Picture (Sample1 - Sample32) above it. I have a small bit a VBA written:

Private Sub Text1_AfterUpdate()
 Dim PicPath As String
 PicPath = "C:\XXX\Images\"
 Me.Sample1.Picture = PicPath & Me.Text1 & ".dib"
End Sub

Open in new window


So after I update the text box, the picture above changes to become whatever image corresponds to that part number. However, my form currently has no record source and it loses the entries when reopened. I was wondering if someone could help me write some code that would keep the entries upon reopening the form.
0
 
LVL 3

Expert Comment

by:DockieBoy
ID: 35041060
Someone else may have a better way to go about this, however, here is my suggestion;

Have a table to store the latest user defined layout.  Store the image layout location (Which control it appears in on your form) and the file path for the image.

You could then have your form update the data in your table using the on change event from a combo box that allows the user to select the part that they wish to display the image of, or, you could have a button that stores all locations once they have decided on the layout eg, a "Save New Layout" button.

I would definately use a combo box to select the part.
0
 

Author Comment

by:Mike McBride
ID: 35056153
DockieBoy, do you know of the best method to go about your plan? I've been using Access for a while, but have only learned that parts that I needed in order to get to where the database is. Therefore, there are many aspects that I do not know. If you could just let me know how to store the layout information into a table, the help would be greatly appreciated. Thanks in advance.
0
 

Accepted Solution

by:
Mike McBride earned 0 total points
ID: 35061570
OK, I got it working. I have a table with 32 fields (one for each part number position). I have this code run on opening the form.
Private Sub Form_Current()
Dim PicPath As String
PicPath = "C:\XX\BMP Images\"
Me.Sample1.Picture = PicPath & Me.[Part1] & ".dib"
End Sub

Open in new window


I then have this code for updating the images.
Private Sub Text1_AfterUpdate()
 Dim PicPath As String
 PicPath = "C:\XX\BMP Images\"
 Me.Sample1.Picture = PicPath & Me.Text1 & ".dib"
End Sub

Open in new window


The 32 text boxes in the form reference the 32 fields in the table. So, when I close the form and reopen it, the part numbers and images are still intact.

Thanks everyone for the help. I'm not sure who to give the points to. I guess a mod can chime in and help me out with that.
0
 

Author Closing Comment

by:Mike McBride
ID: 35126348
Solution I came up with.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now