Query Code and Form

[Russ]

I created a simple database that will be used to track company purchases. I
created three tables (main, list of departments, list of vendors). I created
a query that is based on the main table. From there, I created a form, which
is based on my query, that uses info from the dept and vendor table and
records everything in the main table.

Now, The first field in my Form is PO#. This is a "text" field and the
default value is INDS. I have another field called ID, which is not visible
on the form. This is an Auto number field.

I want to combine the PO field with the ID field so the PO# field
automatically reads
INDS1, next record will be INDS2 and so forth when I use the form. Below is
the code. What is the modified code that I can use to make this work?

SELECT [Purchase Order].[PO#], [Purchase Order].ID, [Purchase
Order].Department, [Purchase Order].Vendor, [Purchase Order].Description,
[Purchase Order].Quantity, [Purchase Order].Cost, [Purchase Order].Shipping,
[Purchase Order].Tax, [Purchase Order].Cost*[Quantity]+[Tax]+[Shipping] AS
[Total Cost]
FROM [Purchase Order];

 

[OfficeDev18 via AccessMonster.com]

Russ,

The best way to concatenate numeric and string values is with the ampersand.
Ergo, you want to say

"INDS" & [Purchase Order].ID As ModID,.....

Sam

I created a simple database that will be used to track company purchases. I
created three tables (main, list of departments, list of vendors). I created
a query that is based on the main table. From there, I created a form, which
is based on my query, that uses info from the dept and vendor table and
records everything in the main table.

Now, The first field in my Form is PO#. This is a "text" field and the
default value is INDS. I have another field called ID, which is not visible
on the form. This is an Auto number field.

I want to combine the PO field with the ID field so the PO# field
automatically reads
INDS1, next record will be INDS2 and so forth when I use the form. Below is
the code. What is the modified code that I can use to make this work?

SELECT [Purchase Order].[PO#], [Purchase Order].ID, [Purchase
Order].Department, [Purchase Order].Vendor, [Purchase Order].Description,
[Purchase Order].Quantity, [Purchase Order].Cost, [Purchase Order].Shipping,
[Purchase Order].Tax, [Purchase Order].Cost*[Quantity]+[Tax]+[Shipping] AS
[Total Cost]
FROM [Purchase Order];

 

[Russ]

Thank you for your help. To be honest with you, I'm not much of an Access
guy. What would my query exactly say? Can you take what I wrote below and
change it so I can just copy and paste it?

Russ,

The best way to concatenate numeric and string values is with the
ampersand.
Ergo, you want to say

"INDS" & [Purchase Order].ID As ModID,.....

Sam

I created a simple database that will be used to track company purchases.
I
created three tables (main, list of departments, list of vendors). I
created
a query that is based on the main table. From there, I created a form,
which
is based on my query, that uses info from the dept and vendor table and
records everything in the main table.

Now, The first field in my Form is PO#. This is a "text" field and the
default value is INDS. I have another field called ID, which is not
visible
on the form. This is an Auto number field.

I want to combine the PO field with the ID field so the PO# field
automatically reads
INDS1, next record will be INDS2 and so forth when I use the form. Below
is
the code. What is the modified code that I can use to make this work?

SELECT [Purchase Order].[PO#], [Purchase Order].ID, [Purchase
Order].Department, [Purchase Order].Vendor, [Purchase Order].Description,
[Purchase Order].Quantity, [Purchase Order].Cost, [Purchase
Order].Shipping,
[Purchase Order].Tax, [Purchase Order].Cost*[Quantity]+[Tax]+[Shipping] AS
[Total Cost]
FROM [Purchase Order];

 

[OfficeDev18 via AccessMonster.com]

SELECT [Purchase Order].[PO#], "INDS" & [Purchase Order].ID As ModID,
[Purchase
Order].Department, [Purchase Order].Vendor, [Purchase Order].Description,
[Purchase Order].Quantity, [Purchase Order].Cost, [Purchase Order].Shipping,
[Purchase Order].Tax, [Purchase Order].Cost*[Quantity]+[Tax]+[Shipping] AS
[Total Cost]
FROM [Purchase Order];

Sam

Russ,

The best way to concatenate numeric and string values is with the ampersand.
Ergo, you want to say

"INDS" & [Purchase Order].ID As ModID,.....

Sam

I created a simple database that will be used to track company purchases. I
created three tables (main, list of departments, list of vendors). I created

[quoted text clipped - 17 lines]

[Total Cost]
FROM [Purchase Order];

 

[Russ]

Sam, thank you very much. We are getting closer. When I open my form I get a
box that says Purchase and it wants me to put in something, not sure. My
goal is to open the form and in the PO# field, I want it to show INDS and
the next number in line. For example, the first record will be INDS1 and
then when I open the form again or enter in a new record, the PO# field will
say INDS2, so forth and so on.

SELECT [Purchase Order].[PO#], "INDS" & [Purchase Order].ID As ModID,
[Purchase
Order].Department, [Purchase Order].Vendor, [Purchase Order].Description,
[Purchase Order].Quantity, [Purchase Order].Cost, [Purchase
Order].Shipping,
[Purchase Order].Tax, [Purchase Order].Cost*[Quantity]+[Tax]+[Shipping] AS
[Total Cost]
FROM [Purchase Order];

Sam

Russ,

The best way to concatenate numeric and string values is with the
ampersand.
Ergo, you want to say

"INDS" & [Purchase Order].ID As ModID,.....

Sam

I created a simple database that will be used to track company purchases.
I
created three tables (main, list of departments, list of vendors). I
created

[quoted text clipped - 17 lines]

[Total Cost]
FROM [Purchase Order];

 

[Russ]

Thank you very much Sam.

Russ,

Take a look at the SQL statement behind the form's Record Source. Make
sure
the words [Purchase Order] are bracketted correctly in all cases. In my
previous post, they got seperated into two lines at one point, so that
might
have something to do with the "Purchase" message you're getting.

As far as generating. PO numbers. You are already using a numeric
autonumber
ID field, so the best way is simply to concatenate the letters and number
as
I showed you previously. You can do this even in a textbox by setting the
textbox's Control Source as follows:

="INDS" & Me.ID

assuming the ID field is already being displayed. If it isn't, make a
textbox
and set the Control Source to the ID field. If you don't want it to
display,
just set the textbox's Visible property to No.

By the way, even if you skip some numbers in the PO number field, it's not
a
problem. Any ID number is only what we were taught way back in the early
'80s
a "meaningless sequential number," because it's only a pointer to a real
object (in this case a PO), it isn't the object itself.

Sam

Sam, thank you very much. We are getting closer. When I open my form I get
a
box that says Purchase and it wants me to put in something, not sure. My
goal is to open the form and in the PO# field, I want it to show INDS and
the next number in line. For example, the first record will be INDS1 and
then when I open the form again or enter in a new record, the PO# field
will
say INDS2, so forth and so on.

SELECT [Purchase Order].[PO#], "INDS" & [Purchase Order].ID As ModID,
[Purchase

[quoted text clipped - 24 lines]

[Total Cost]
FROM [Purchase Order];