Dynamic query sort of thing

[halfbodyguy]

OK, I am new and tasked with making a dispatch database.


We have customer numbers such as 100443.002


in that the .002 is the delivery address and the 100433 is the specific

person or company.


I need to have a setup where when filling in the form information, I
can enter the customer number, then have the Delivery location be a
list of ONLY delivery locations tied to that Customer.


For example with each customer I have fields set up as such:


ADDRESSTABLE


CUSTOMERNUMBER DELIVERYNUMBER ADDRESS
110029 001 123Wherever St
110029 002 865 Myhouse st
110040 001 thisplace road
110040 003 myoldplace


ORDERTABLE


ORDER NUMBER CUSTOMER NUMBER DELIVERYNUMBER OTHERFIELDS
5540019 110029 002(dropdown) xxxxxxxxxxx
5540020 110040 003(dropdown) xxxxxxxxxxx


Now i want to select the customer number in a form (or preferably setup

from the table) and then when I go to the address field, the only
options I have are those with the same customer number as the field I
am working in.


I assume this will probably require a query, but I can't quite seem to
get it right... Any Ideas?

 

[Michel Walsh]

Hi,


EVEN IF the customer number is as you described, you clearly USE it as TWO
pieces of information. You should STORE the data in TWO fields (I would name
them as delivery and specific) and PRESENTS IT in your form and report as
every one is used to see that "information".

It will be EASY to work with the data stored as described, a simple WHERE
clause will limit the list displayed by the combo box. Have the combo box
RowSource property with something like:

SELECT specific, address FROM Address WHERE
delivery=Nz(FORMS!formName!deliveryControl, delivery)


Now, for presentation (in forms or in reports), use a query, such as:


SELECT specific & "." & delivery As fullCustomerNumber, .... FROM ...


and base the form/report on that query rather than on a table.



Hoping it may help,
Vanderghast, Access MVP

 

[George Nicholson]

We have customer numbers such as 100443.002

First, lets clarify this. From what you laid out, I don't think the above is
100% accurate. Your *data* actually has Customer Numbers like 100443 and
Delivery Numbers (i.e., locations) like 002. You may combine them on reports
& such and then informally call that 100443.002 combination "Customer
Number", but that informal definition of gets in the way of this discussion
(let's keep it formal <g>). Onward...

For your DeliveryNumber combo box:
ControlSource: DeliveryNumber
RowSourceType: Table/Query
RowSource:
SELECT [DeliveryNumber], [Address] FROM AddressTable WHERE
[CustomerNumber] = Nz(FORMS!formName!CustomerNumber,0);
ColumnCount: 2

In cboCustomerNumber_AfterUpdate event:
Me.cboDeliveryNumber.Requery
Me.cboDeliveryNumber.SetFocus
(this changes the available DeliveryNumber options when you change Customer
Number)

In Form_Current event:
Me.cboDeliveryNumber.Requery
(this changes the available options when simply moving from one record to
the next with no changes to Customer Number)

A slightly different approach:
http://support.microsoft.com/default.aspx?scid=kb;en-us;209576

HTH,

 

[halfbodyguy]

I'm Still having trouble, but i might need to clarify slightly to make
this easier. I'll re-explain


the customer numbers ARE such as 14433.000 but NOT IN THE DATA, sorry
about that.

In the Data, they are stored such as 14433 i the CustomerNumber Field,
and the 000 in a Customer Location field, as it is the number tied to a
specific address.


first table:

"Customer Name"

Fields= CustomerNumber, CustomerName


"CustomerLocation"

Fields= CustomerNumber, CustomerLocation, Address
(the customerNumber is a one to many based on the customer name lookup)

Then there is an "OrderNumber" Table, which has:

Fields= OrderNumber, CustomerNumber, CustomerLocation, etc.

Now I am Brand new to access, with only 14 hours of training, and SQL
is not a strong suit of mine, so I have a little trouble
differentiating the coding from the "insert here" in the sql, so a
little explanation would be cool, cause I think I am leaving in things
that are supposed to be replaced with stuff out of my database.

Otherwise, same task is at hand... Thanks for bearing with me. I'm
becoming addicted to solving this!

 

[halfbodyguy]

Got it! All solved, thanks guys