A general "How would you code this" question.

[Brave]

I am hoping for a code example of how to do this, and hopefully it will
help me to see an easy way to code what seems to be a huge monster.

I need to create a form that has two pulldown menues (field names
Option1 and Option2). Each will house a list of the 50 U.S. States.
Users would then be able to choose two states, and get a listing for a
courier used for pickup\deliveries (exp: My order is in Ohio, but I
need it delivered to Maine. Who do I use?)

I am not as familiar with code as I would like, but it seems I would
need something like this:

If option1 = this, and option2 = this, then the answer (field name
Result) = this

I know that looks stupid, but I am hoping to show you how I am
approching this in my head.

Any ideas on how you would approch this project would be great. If you
could offer a sample of code, I'm sure I could use it as a template for
other projects. My idea is that this is just standard IF this THEN that
coding, but I don't know the basics enough to create the code from
scratch.

Thanks for any time you can give.

 

[Slim]

I am hoping for a code example of how to do this, and hopefully it will
help me to see an easy way to code what seems to be a huge monster.

I need to create a form that has two pulldown menues (field names
Option1 and Option2). Each will house a list of the 50 U.S. States.
Users would then be able to choose two states, and get a listing for a
courier used for pickup\deliveries (exp: My order is in Ohio, but I
need it delivered to Maine. Who do I use?)

I am not as familiar with code as I would like, but it seems I would
need something like this:

If option1 = this, and option2 = this, then the answer (field name
Result) = this

choosing the states and then submitting the form would give you the 2
values. that is the easy part.
Now how do you expect to select the best courier from this information?

You would need some sort of lookup table.

 

[Brave]

Thanks for the reply. I will be given an excel sheet that shows (much
like looking at a map) what courier is used.

States are listed down the side and accross the top of the excel sheet.
(exp: Ohio and Maine = UPS)

 

[Slim]

Thanks for the reply. I will be given an excel sheet that shows (much
like looking at a map) what courier is used.

States are listed down the side and accross the top of the excel sheet.
(exp: Ohio and Maine = UPS)

ok
well i would load the data into a database, but you can read straight from
the excel file also.

you need to make a connection to the data

then use a SQL query to select the answere and reder it to the client.

do you know how to make a connection to a excel sheet?

do you know how to make a SQL query?

 

[Brave]

I have done some simple update queries, but nothing very advanced. I
have never connected to an excel sheet

Here is an example of a connection to an MS Access db I did. Would it
structure itself in a similar way?

<%
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "driver={Microsoft Access Driver
(*.mdb)};;DBQ=\\*********Path to database*******.mdb;"
mySQL="INSERT INTO Table (FieldName) VALUES (Option1)"
Conn.Execute(mySQL)
Conn.Close
Set Conn = Nothing
%>

 

[Mike Brind]

Hmmm. For connecting to Access, you are better off using the OLEDB driver
rather than the ODBC driver in your example

Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;

Excel is pretty much the same:

Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=Fullpath\MyExcel.xls;Extended Properties=""Excel
8.0;HDR=Yes;IMEX=1"""

HDR=Yes indicates that the worksheet has a header row. Remove IMEX=1 if you
want to write to the worksheet.

sql="SELECT field1, field2 FROM [Sheet1$]" is an example select statement.
Notice that the worksheet name is followed by a $ sign.

 

[Slim]

Better still

I would upload my data into access, is there a reason for the data staying
in excel format?

Hmmm. For connecting to Access, you are better off using the OLEDB driver
rather than the ODBC driver in your example

Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=\somepath\mydb.mdb;

Excel is pretty much the same:

Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=Fullpath\MyExcel.xls;Extended Properties=""Excel
8.0;HDR=Yes;IMEX=1"""

HDR=Yes indicates that the worksheet has a header row. Remove IMEX=1 if
you want to write to the worksheet.

sql="SELECT field1, field2 FROM [Sheet1$]" is an example select statement.
Notice that the worksheet name is followed by a $ sign.

--
Mike Brind

I have done some simple update queries, but nothing very advanced. I
have never connected to an excel sheet

Here is an example of a connection to an MS Access db I did. Would it
structure itself in a similar way?

<%
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "driver={Microsoft Access Driver
(*.mdb)};;DBQ=\\*********Path to database*******.mdb;"
mySQL="INSERT INTO Table (FieldName) VALUES (Option1)"
Conn.Execute(mySQL)
Conn.Close
Set Conn = Nothing
%>

 

[Brave]

No, my plan is to do it in access. I was just wondering about the
specifics. I'm assuming that functionality may come in handy on a
future project.

Thanks for everything.

Better still

I would upload my data into access, is there a reason for the data staying
in excel format?

Hmmm. For connecting to Access, you are better off using the OLEDB driver
rather than the ODBC driver in your example

Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=\somepath\mydb.mdb;

Excel is pretty much the same:

Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=Fullpath\MyExcel.xls;Extended Properties=""Excel
8.0;HDR=Yes;IMEX=1"""

HDR=Yes indicates that the worksheet has a header row. Remove IMEX=1 if
you want to write to the worksheet.

sql="SELECT field1, field2 FROM [Sheet1$]" is an example select statement.
Notice that the worksheet name is followed by a $ sign.