UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Drop Down List, Access 2016    
 
   
Joeman364
post Jun 4 2020, 08:44 PM
Post#1



Posts: 21
Joined: 26-February 13



Hi UtterAccess,

I was wondering if you can tell me how this data base that I found when you select the customer id name combo box it filled in the rest of the information? I need to do something similar to a database I am working on. Is there any videos or articles on this? Thank you for your help...Joe from Michigan
This post has been edited by Joeman364: Jun 4 2020, 08:45 PM
Attached File(s)
Attached File  Combo_Boxes_Template_AFTER.zip ( 34.64K )Number of downloads: 5
Attached File  Combo_Boxes_Template_AFTER.zip ( 34.64K )Number of downloads: 1
 
Go to the top of the page
 
June7
post Jun 4 2020, 10:29 PM
Post#2



Posts: 1,512
Joined: 25-January 16
From: The Great Land


Combobox has an Embedded Macro that applies filter to form.

I expect it was programmed by wizard when combobox was added to form. https://www.techonthenet.com/access/tutoria...10/basics03.php

This post has been edited by June7: Jun 4 2020, 10:32 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
RJD
post Jun 4 2020, 10:44 PM
Post#3


UtterAccess VIP
Posts: 10,566
Joined: 25-October 10
From: Gulf South USA


Hi Joe: Well, it really doesn't "fill in" the other information. The Macro (which I converted to a VBA procedure below since it is, IMHO, easier to work with) simply goes to (searches for) the first record it can find that has the customer id you selected. You cannot see the customer id in the combobox even though it is there (it is not shown in the combobox - but could be if you wanted it to), but you see the customer name. When you select the name, you are actually selecting the row with the customer id (the first column). The After_Update event procedure, which performs the search, takes over when you select a row and takes you to the record with the matching customer id ... and all the controls are already filled with the related field values.

In other words, you are just finding the record from the table that is already available in the form. You could find in by just advancing from one record to the next using the navigation buttons on the form. What is set up is just a shortcut way to get to the record (customer) you want.

CODE
Private Sub Combo17_AfterUpdate()
    DoCmd.SearchForRecord , "", acFirst, "[CustomerID] = " & Nz(Me!Combo17,0)
End Sub

I changed the code a bit to make it easier to digest, using Me!Combo17 instead of the ActiveControl command from the db you posted.

Is this what you are trying to accomplish in the db you are working on? Or do you want something else?

Anyway, "Is there any videos or articles on this?" Yes. Here's your article, specific to this task...

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
tina t
post Jun 4 2020, 11:44 PM
Post#4



Posts: 6,671
Joined: 11-November 10
From: SoCal, USA


QUOTE
Anyway, "Is there any videos or articles on this?" Yes. Here's your article, specific to this task...

hi, Joe! :) just wondering, did you mean to include a link in your last post?

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
Joeman364
post Jun 5 2020, 05:46 AM
Post#5



Posts: 21
Joined: 26-February 13



Hi Tina,

Thanks for replying to my post. I was looking for any videos or articles regarding when you select an item from a drop down list it would it would automatic other fields that are consistant with that combo box. For example, if I select a customer name in the combo box on the form it would fill in the address, city, state, zip, phone number. So that I wouldn't have to type in the rest of the field boxes.
Go to the top of the page
 
RJD
post Jun 5 2020, 08:54 AM
Post#6


UtterAccess VIP
Posts: 10,566
Joined: 25-October 10
From: Gulf South USA


Hi tina: wavehi.gif Well, I worded that part of the last post badly. I didn't mean to post a link to an article or video - I meant to just say that the post itself is the article, created for the specifics of the question the OP asked. My late-night fog, I guess. Thanks for helping me clarify.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
RJD
post Jun 5 2020, 09:10 AM
Post#7


UtterAccess VIP
Posts: 10,566
Joined: 25-October 10
From: Gulf South USA


Hi Joe (from Michigan): I think we need some clarification on your requirement vs the db you posted.

The posted db uses the combobox to identify a customer to search for. That is, the records are already present in the form (from the table). There is NO "filling in" of values in the controls ... they are already "filled in" because they are bound to fields in records. You are just selecting the record to move to and display on the form, the form being bound to the customer table.

But it sounds like you might want to have a form that is NOT bound to customer table records - and you want to create a new record by gathering the field values in a new table from an existing customer table. This is not what is being done in your posted db template. And if the records already exist elsewhere, readily available, the additional controls should not be bound to the new table but only be linked by the customer id. That's what relational databases do. If you want to just display the values, that can be done by linkage in a query and not by storing in two separate places.

So, my confusion about your purpose. Could you explain a bit more, reflecting on the differences in requirements explained above?

HTH
Joe


--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Joeman364
post Jun 5 2020, 12:40 PM
Post#8



Posts: 21
Joined: 26-February 13



Hi fellow Joe,

I didn't mean to sound confusing...I am sending the attached file on the database I am working on. Can you take a look at it when you have the time? I guess what I am looking for is when a new record is issued, and you choose and item from the drop down list, I would like to have the following autopopulate: category, room, location, price per item. These field boxes won't change, and I don't want to look at the table lab supplies everytime to get the information. Plus, in the Item Name combo box I would like not to have duplicates in the selection. Anyway, thanks for helping me out.
Attached File(s)
Attached File  JLHGuardianship.zip ( 100.71K )Number of downloads: 3
 
Go to the top of the page
 
RJD
post Jun 5 2020, 01:22 PM
Post#9


UtterAccess VIP
Posts: 10,566
Joined: 25-October 10
From: Gulf South USA


Hi Joe: I'm still a bit confused. You have tbllabsupplies bound to frmlabsupplies. It looks like the tbllabsupplies is a catalog of supplies. Are you adding a new lab supplies record to the catalog? If so, what would be the source for the field values in that case? If you are making a sales and shipping record, then you need a new table for that (with a new form), and the data on sales and shipping should be separated from the tbllabsupplies catalog record, say tblSales, linked 1-many. And on a new form bound to sales you can choose the item and easily display and/or insert data from the catalog, which I intuit where you are trying to go with this.

Why not back up and tell us what you are trying to accomplish with this db. Such as ...

- keep track of catalog items and associated inventory
- keep track of vendors who supply lab supplies
- keep track of sales and shipping of lab supplies

Note that you should probably also have a table of inventory as well, separated from the catalog, so that you can track increases in inventory (purchases and returns, for example) (decreases might come from sales).

Then you can adjust your design to accommodate these objectives. As it stands now it looks like you have design issues that have to be solved first.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
June7
post Jun 5 2020, 01:54 PM
Post#10



Posts: 1,512
Joined: 25-January 16
From: The Great Land


Ooops, wrong thread.

This post has been edited by June7: Jun 5 2020, 02:22 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
RJD
post Jun 5 2020, 02:15 PM
Post#11


UtterAccess VIP
Posts: 10,566
Joined: 25-October 10
From: Gulf South USA


June7: Are you sure you are posting to the correct thread? I don't see your suggestions related to the OP's attached db... Am I missing something?

Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
RJD
post Jun 5 2020, 02:21 PM
Post#12


UtterAccess VIP
Posts: 10,566
Joined: 25-October 10
From: Gulf South USA


Joe: While you are cogitating about design, I just wanted to correct what you were doing in your posted db. You put the combobox in the body of the form and bound it to the item name. This will simply change the item name for whatever item is displayed. To SEARCH for the item, put the combobox in the header and do NOT make it bound. Then in After_Update for the combobox search for the record with that item name. See the revision to your db attached. This will "fill in" the fields when the record is found - automatically, since you are simply going to that record that is already there.

But this DOES NOT FIX your design issues overall. See my previous post on this, and spend some time studying proper relational db design. Otherwise you will be fighting problems with this db from now on.

HTH
Joe
Attached File(s)
Attached File  JLHGuardianship_Rev1.zip ( 44.24K )Number of downloads: 5
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Joeman364
post Jun 5 2020, 02:24 PM
Post#13



Posts: 21
Joined: 26-February 13



Hi,
I will try your suggestions, thanks for the advice...
Go to the top of the page
 
RJD
post Jun 5 2020, 02:26 PM
Post#14


UtterAccess VIP
Posts: 10,566
Joined: 25-October 10
From: Gulf South USA


You are welcome. Do some design work/re-thinking, then when you have more questions let us know.

Regards,
joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Joeman364
post Jun 6 2020, 09:31 AM
Post#15



Posts: 21
Joined: 26-February 13



Hi Joe,

I did some modifications on my form. I'm not sure if I properly designed this the right way. I made some of the fields combo boxes, ran queries to put the list in them. It seems to help out when I'm entering new records so I don't have to look at the table all the time. One thing though the ID isn't working when I put in a new record. Can you take a look at it and see what's going on with that? Thanks, Joe.
Attached File(s)
Attached File  JLHGuardianship__2_.zip ( 55.21K )Number of downloads: 4
 
Go to the top of the page
 
mike60smart
post Jun 6 2020, 11:37 AM
Post#16


UtterAccess VIP
Posts: 14,016
Joined: 6-June 05
From: Dunbar,Scotland


Hi Joe

Your Form is based on the Supplies table which is the Child Table.

You will not be able to add a Vendor ID using this Form.

Normal setup would be a Main Form based on Vendor and then a Subform based onSupplies


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
RJD
post Jun 8 2020, 11:40 AM
Post#17


UtterAccess VIP
Posts: 10,566
Joined: 25-October 10
From: Gulf South USA


Hi Michigan Joe: Just getting back to this after buttoning up and dealing with the tropical storm for a while (wind, rain, high water, internet and power issues). Mostly by us now with minor damage. No biggie in this area. Just debris to pick up.

As to your database, I encourage you to step back from what you are doing with your db and learn about designing this type db. You are making some fundamental mistakes, and that is causing your difficulty at this point. You have a table of lab supplies that mixes a list of supplies with sales of those supplies in the same table. These are two separate things and should not be mixed. Two separate tables here. Yes, the sale needs a reference to the lab supply list, but that is what a relational db does. But you also need a customer list and several utility tables to list things like rooms, locations, categories. At least that's what it looks like at this point.

I had suggested before that you revisit your design. Sketch it out on paper - what are the fundamental records needed (table design) and how do they relate to one another. What utility lists will be needed. What do you want to accomplish with this db? What data to track, how data are to be entered and maintained, what reports are needed, etc. Will you use this for invoicing as well?

Solving your last question will not advance your design. It will only confuse things more. Sorry to be the bearer of unpleasant news, but the more time you spend getting the design right, the easier will be the building and operating of your application. Do some research on this type db, see if you can find a template or example somewhere, in books or on line, to guide you.

Best of luck with your project. When you have learned more about this type db and have a better design in mind, if you have questions at that point you can run them by us here.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    4th July 2020 - 01:23 PM