linked text box to table

[Guest]

I have a workorder database that I'm tying to improve.

Form: Workorders
Table: Workorders

In my form, I've created two combo box controls.
Make/Model(1)
Make/Model(2)

The combo box pull down menu displays the customers current list of loaned
equipment allowing the administrator to pick one.

Once a coffee machine is selected, I have the serial number automatically
populate a separate text box right next to the combo box
(=MakeAndModel1.Column(2))

The two serial number txt boxes are:
txtSN1
txtSN2

I'm trying to write these two fields to the workorders table along with both
make/model fields. The make/model fields are working, I'm struggling with the
serial number fields...

Thx in advance.

 

[pietlinden]

I have a workorder database that I'm tying to improve.

Form: Workorders
Table: Workorders

In my form, I've created two combo box controls.
Make/Model(1)
Make/Model(2)

The combo box pull down menu displays the customers current list of loaned
equipment allowing the administrator to pick one.

Once a coffee machine is selected, I have the serial number automatically
populate a separate text box right next to the combo box
(=MakeAndModel1.Column(2))

The two serial number txt boxes are:
txtSN1
txtSN2

I'm trying to write these two fields to the workorders table along with both
make/model fields. The make/model fields are working, I'm struggling with the
serial number fields...

Thx in advance.

Why not just wrote the serial number? You can use that to determine
the make/model, can you not?

 

[Beetle]

I have a workorder database that I'm tying to improve.

Form: Workorders
Table: Workorders

In my form, I've created two combo box controls.
Make/Model(1)
Make/Model(2)

The combo box pull down menu displays the customers current list of loaned
equipment allowing the administrator to pick one.

Once a coffee machine is selected, I have the serial number automatically
populate a separate text box right next to the combo box
(=MakeAndModel1.Column(2))

The two serial number txt boxes are:
txtSN1
txtSN2

I'm trying to write these two fields to the workorders table along with both
make/model fields. The make/model fields are working, I'm struggling with the
serial number fields...

Thx in advance.

Presumably, if the serial number for a product appears in one of the
columns of you combo box query, then that serial number is already
stored in one of the tables in your DB. Your query had to get it from
somewhere (maybe not the Workorders table, but some other table). You
would not normally want to store the same serial number in two
different tables. Just have it appear on your Workorders form (which
it sounds like it is already doing).

HTH

 

[Guest]

I understand, but if I want to report on the workorders, this becomes a
problem. A customer could easily have two identical coffee machines. The only
difference between the two would be the serial numbers. So, if I write up a
workorder for one of those machines, I would have no way of displaying the
serial number (unless I'm missing something?)

The amount of extra data stored in the database would be minimal. Is there
no way to carry this type of txt field over to a table?

 

[pietlinden]

I understand, but if I want to report on the workorders, this becomes a
problem. A customer could easily have two identical coffee machines. The only
difference between the two would be the serial numbers. So, if I write up a
workorder for one of those machines, I would have no way of displaying the
serial number (unless I'm missing something?)

The amount of extra data stored in the database would be minimal. Is there
no way to carry this type of txt field over to a table?

sure you do. stuff the description in a (maybe hidden) column in your
combobox and then create an unbound textbox and set its control source
to =cboItem.Columns(1) or whichever contains the description of the
item.

 

[Guest]

Hello JK

To answer your question, yes there is a method of storing this type of data
in a table field. To use the column method like you are, the textbox needs to
be unbound. In other words it's not bound directly to a field in a table or
query. Thats why when you save your data, the value in the text box doesn't
save, because Access has no way of knowing where to save it. In order to save
the value in an unbound contol like your text box, you have to use code to
tell Access where to save it. The code might look something like;

Dim rst AS Recordset

set rst = CurrentDb.OpenRecordSet (Select * From tblWorkOrders Where
ProductID=" & Me![ProductID])

rst.AddNew

rst!SerialNumber = Me!SerialNumber

rst.Update
rst.Close

Set rst = Nothing

This is sort of the readers digest version (normally there would be a little
more to code like this). Basically this creates a recordset copy of your
table, inserts the value from your unbound text box to a field in the
recordset, then saves it to a field in your table.

However, I don't now if you could even use this method. This is typically
used in situations where you only need to save one field, or maybe where the
entire form and all of it's fields are UNBOUND. If the other fields on your
form are BOUND to a query or table field, then you would have a situation
where you are trying to save some fields directly while trying to save the
text box value indirectly (via code) all to the same record in your table. I
don't know if this is even possible.

Again, I will reiterate that it is not necessary to do this. If the serial
number ealready exists in one of your tables, then all you need to do is
*look it up* when you need it. There are different ways of doing this. You
can use the Column method (which you already are doing). You could also use
the DLookup function.There are probably other ways that aren't coming to mind
right now.

So basically, my advice is don't try to save the serial number to the
WorkOrders table. I wen't to the trouble of explaining all this because, if
you are anything like me , you don't just want someone to tell you how to do
something, you also want to know WHY (dammit)

HTH

 

[Guest]

First of all, thank you both for the time you've invested in this issue I'm
having...
pietlinden, I tried that - it doesn't carry over to the workorders table
like I thought it would.

Beetle, I really appreciate the time you took to explain bound vs. unbound.
I get heavily involved with access for a few months out of any given year and
then I don't touch it. Some of these concepts are lost during that interim
period.

Regarding your suggested solution, I think that is a complicated way to
carry data from a form to a table. Especially since my expertise with VBA is
pathetic to say the least.

Is this really so complicated? I want to carry a field to a table from a
form. The data is stored in two locations on the form. It seems the txt box
bound to the combo box is not going to work (for whatever reason.) But, as I
said, I am able to carry the make/model field (cbo) over to the workorders
table.

In the Row Source of my combo box, I have three columns:

0 - Account Number; with criteria to pull all equipment in the equipment
table with that account number.
1 - Equip Description
2 - Serial Number

If I can write the Equip Description field to the workorders table, can't I
write the serial number field as well? Or, should I give up on this and go
back to making the user manually type in the equipment description & serial
number?

Hello JK

To answer your question, yes there is a method of storing this type of data
in a table field. To use the column method like you are, the textbox needs to
be unbound. In other words it's not bound directly to a field in a table or
query. Thats why when you save your data, the value in the text box doesn't
save, because Access has no way of knowing where to save it. In order to save
the value in an unbound contol like your text box, you have to use code to
tell Access where to save it. The code might look something like;

Dim rst AS Recordset

set rst = CurrentDb.OpenRecordSet (Select * From tblWorkOrders Where
ProductID=" & Me![ProductID])

rst.AddNew

rst!SerialNumber = Me!SerialNumber

rst.Update
rst.Close

Set rst = Nothing

This is sort of the readers digest version (normally there would be a little
more to code like this). Basically this creates a recordset copy of your
table, inserts the value from your unbound text box to a field in the
recordset, then saves it to a field in your table.

However, I don't now if you could even use this method. This is typically
used in situations where you only need to save one field, or maybe where the
entire form and all of it's fields are UNBOUND. If the other fields on your
form are BOUND to a query or table field, then you would have a situation
where you are trying to save some fields directly while trying to save the
text box value indirectly (via code) all to the same record in your table. I
don't know if this is even possible.

Again, I will reiterate that it is not necessary to do this. If the serial
number ealready exists in one of your tables, then all you need to do is
*look it up* when you need it. There are different ways of doing this. You
can use the Column method (which you already are doing). You could also use
the DLookup function.There are probably other ways that aren't coming to mind
right now.

So basically, my advice is don't try to save the serial number to the
WorkOrders table. I wen't to the trouble of explaining all this because, if
you are anything like me , you don't just want someone to tell you how to do
something, you also want to know WHY (dammit)

HTH
--
_________

Sean Bailey

I understand, but if I want to report on the workorders, this becomes a
problem. A customer could easily have two identical coffee machines. The only
difference between the two would be the serial numbers. So, if I write up a
workorder for one of those machines, I would have no way of displaying the
serial number (unless I'm missing something?)

The amount of extra data stored in the database would be minimal. Is there
no way to carry this type of txt field over to a table?

 

[Beetle]

First of all, thank you both for the time you've invested in this issue I'm
having...
pietlinden, I tried that - it doesn't carry over to the workorders table
like I thought it would.

Beetle, I really appreciate the time you took to explain bound vs. unbound.
I get heavily involved with access for a few months out of any given year and
then I don't touch it. Some of these concepts are lost during that interim
period.

Regarding your suggested solution, I think that is a complicated way to
carry data from a form to a table. Especially since my expertise with VBA is
pathetic to say the least.

Is this really so complicated? I want to carry a field to a table from a
form. The data is stored in two locations on the form. It seems the txt box
bound to the combo box is not going to work (for whatever reason.) But, as I
said, I am able to carry the make/model field (cbo) over to the workorders
table.

In the Row Source of my combo box, I have three columns:

0 - Account Number; with criteria to pull all equipment in the equipment
table with that account number.
1 - Equip Description
2 - Serial Number

If I can write the Equip Description field to the workorders table, can't I
write the serial number field as well? Or, should I give up on this and go
back to making the user manually type in the equipment description & serial
number?

Hello JK

To answer your question, yes there is a method of storing this type of data
in a table field. To use the column method like you are, the textbox needs to
be unbound. In other words it's not bound directly to a field in a table or
query. Thats why when you save your data, the value in the text box doesn't
save, because Access has no way of knowing where to save it. In order to save
the value in an unbound contol like your text box, you have to use code to
tell Access where to save it. The code might look something like;

Dim rst AS Recordset

set rst = CurrentDb.OpenRecordSet (Select * From tblWorkOrders Where
ProductID=" & Me![ProductID])

rst.AddNew

rst!SerialNumber = Me!SerialNumber

rst.Update
rst.Close

Set rst = Nothing

This is sort of the readers digest version (normally there would be a little
more to code like this). Basically this creates a recordset copy of your
table, inserts the value from your unbound text box to a field in the
recordset, then saves it to a field in your table.

However, I don't now if you could even use this method. This is typically
used in situations where you only need to save one field, or maybe where the
entire form and all of it's fields are UNBOUND. If the other fields on your
form are BOUND to a query or table field, then you would have a situation
where you are trying to save some fields directly while trying to save the
text box value indirectly (via code) all to the same record in your table. I
don't know if this is even possible.

Again, I will reiterate that it is not necessary to do this. If the serial
number ealready exists in one of your tables, then all you need to do is
*look it up* when you need it. There are different ways of doing this. You
can use the Column method (which you already are doing). You could also use
the DLookup function.There are probably other ways that aren't coming to mind
right now.

So basically, my advice is don't try to save the serial number to the
WorkOrders table. I wen't to the trouble of explaining all this because, if
you are anything like me , you don't just want someone to tell you how to do
something, you also want to know WHY (dammit)

Sean Bailey

"JK" wrote:

- Show quoted text -

If I can write the Equip Description field to the workorders table, can't I
write the serial number field as well? Or, should I give up on this and go
back to making the user manually type in the equipment description & serial
number?

You should do neither.

Here is what I've gathered about your DB from your posts so far (this
may not be exact, but hopefully it's close to what you have).

You have an Accounts table (or something similar) that stores
information about who you provide equipment to, a Workorders table,
and an Equipment table. You table structure might look something like;

tblAccounts
**************
AccountID (Primary Key)
AccountName
Address
Phone
Other fields related to the account

tblWorkOrders
*****************
WorkOrderID (PK)
AccountID (Foreign Key to the Accounts table)
EquipmentID (FK to the Equipment table)
OrderDate
Other fields related specifically to the workorder

tblEquipment
****************
EquipmentID
EquipmentDescription
SerialNumber
Other fields related to each piece of equipment

As far as your equipment goes, the only value that needs to be stored
in tblWorkOrders is the EquipmentID as a foreign key to tblEquipment.
There is absolutely no reason to store the description or the serial
number in tblWorkOrders. You only need to lookup those fields when you
need them. You just tell Access "show me the description and serial
number that match the EquipmentID for this work order". You do this
via some of the methods that we have already covered (Column method,
DLookup, etc.). The values then get displayed in your form, on your
report, etc. You seem to be under the impression that just because a
value is displayed in your work orders form, it has to be stored in
your work orders table. That is not the case. It's already stored in
your equipment table and you can retrieve it whenever you need to.

If I've completely misunderstood your DB structure or what you are
trying to do let me know.

HTH

 

[Guest]

This is excatly my problem and why I think I need to store the data in the
workorders table. The Equipment table is updated weekly using an odbc driver
to J.D. Edwards. I have a txt file that is updated when a report in Edwards
is run (this txt file lives on a company server.) Then, I have a linked table
to that txt file & I have a make table query setup to replace the equipment
table with the data that is in the linked table. This keeps the information
up-to-date.

Unfortunately, I'm not savvy enough to figure out how to do an update /
append vrs. a make table query when there is no primary key in the linked
table. As I said in a previous post, the account number is listed as many
times as there is equipment on loan. There is no single field that's not
duplicated at some point. Even if I add a primary key to the table, it would
be blown away when I run the make table query.

First of all, thank you both for the time you've invested in this issue I'm
having...
pietlinden, I tried that - it doesn't carry over to the workorders table
like I thought it would.

Beetle, I really appreciate the time you took to explain bound vs. unbound.
I get heavily involved with access for a few months out of any given year and
then I don't touch it. Some of these concepts are lost during that interim
period.

Regarding your suggested solution, I think that is a complicated way to
carry data from a form to a table. Especially since my expertise with VBA is
pathetic to say the least.

Is this really so complicated? I want to carry a field to a table from a
form. The data is stored in two locations on the form. It seems the txt box
bound to the combo box is not going to work (for whatever reason.) But, as I
said, I am able to carry the make/model field (cbo) over to the workorders
table.

In the Row Source of my combo box, I have three columns:

0 - Account Number; with criteria to pull all equipment in the equipment
table with that account number.
1 - Equip Description
2 - Serial Number

If I can write the Equip Description field to the workorders table, can't I
write the serial number field as well? Or, should I give up on this and go
back to making the user manually type in the equipment description & serial
number?

Hello JK

To answer your question, yes there is a method of storing this type of data
in a table field. To use the column method like you are, the textbox needs to
be unbound. In other words it's not bound directly to a field in a table or
query. Thats why when you save your data, the value in the text box doesn't
save, because Access has no way of knowing where to save it. In order to save
the value in an unbound contol like your text box, you have to use code to
tell Access where to save it. The code might look something like;

Dim rst AS Recordset

set rst = CurrentDb.OpenRecordSet (Select * From tblWorkOrders Where
ProductID=" & Me![ProductID])

rst.AddNew

rst!SerialNumber = Me!SerialNumber

rst.Update
rst.Close

Set rst = Nothing

This is sort of the readers digest version (normally there would be a little
more to code like this). Basically this creates a recordset copy of your
table, inserts the value from your unbound text box to a field in the
recordset, then saves it to a field in your table.

However, I don't now if you could even use this method. This is typically
used in situations where you only need to save one field, or maybe where the
entire form and all of it's fields are UNBOUND. If the other fields on your
form are BOUND to a query or table field, then you would have a situation
where you are trying to save some fields directly while trying to save the
text box value indirectly (via code) all to the same record in your table. I
don't know if this is even possible.

Again, I will reiterate that it is not necessary to do this. If the serial
number ealready exists in one of your tables, then all you need to do is
*look it up* when you need it. There are different ways of doing this. You
can use the Column method (which you already are doing). You could also use
the DLookup function.There are probably other ways that aren't coming to mind
right now.

So basically, my advice is don't try to save the serial number to the
WorkOrders table. I wen't to the trouble of explaining all this because, if
you are anything like me , you don't just want someone to tell you how to do
something, you also want to know WHY (dammit)

Sean Bailey

"JK" wrote:

I understand, but if I want to report on the workorders, this becomes a
problem. A customer could easily have two identical coffee machines. The only
difference between the two would be the serial numbers. So, if I write up a
workorder for one of those machines, I would have no way of displaying the
serial number (unless I'm missing something?)

The amount of extra data stored in the database would be minimal. Is there
no way to carry this type of txt field over to a table?

"Beetle" wrote:

I have a workorder database that I'm tying to improve.

Form: Workorders
Table: Workorders

In my form, I've created two combo box controls.
Make/Model(1)
Make/Model(2)

The combo box pull down menu displays the customers current list of loaned
equipment allowing the administrator to pick one.

Once a coffee machine is selected, I have the serial number automatically
populate a separate text box right next to the combo box
(=MakeAndModel1.Column(2))

The two serial number txt boxes are:
txtSN1
txtSN2

I'm trying to write these two fields to the workorders table along with both
make/model fields. The make/model fields are working, I'm struggling with the
serial number fields...

Thx in advance.

Presumably, if the serial number for a product appears in one of the
columns of you combo box query, then that serial number is already
stored in one of the tables in your DB. Your query had to get it from
somewhere (maybe not the Workorders table, but some other table). You
would not normally want to store the same serial number in two
different tables. Just have it appear on your Workorders form (which
it sounds like it is already doing).

HTH- Hide quoted text -

- Show quoted text -

If I can write the Equip Description field to the workorders table, can't I
write the serial number field as well? Or, should I give up on this and go
back to making the user manually type in the equipment description & serial
number?

You should do neither.

Here is what I've gathered about your DB from your posts so far (this
may not be exact, but hopefully it's close to what you have).

You have an Accounts table (or something similar) that stores
information about who you provide equipment to, a Workorders table,
and an Equipment table. You table structure might look something like;

tblAccounts
**************
AccountID (Primary Key)
AccountName
Address
Phone
Other fields related to the account

tblWorkOrders
*****************
WorkOrderID (PK)
AccountID (Foreign Key to the Accounts table)
EquipmentID (FK to the Equipment table)
OrderDate
Other fields related specifically to the workorder

tblEquipment
****************
EquipmentID
EquipmentDescription
SerialNumber
Other fields related to each piece of equipment

As far as your equipment goes, the only value that needs to be stored
in tblWorkOrders is the EquipmentID as a foreign key to tblEquipment.
There is absolutely no reason to store the description or the serial
number in tblWorkOrders. You only need to lookup those fields when you
need them. You just tell Access "show me the description and serial
number that match the EquipmentID for this work order". You do this
via some of the methods that we have already covered (Column method,
DLookup, etc.). The values then get displayed in your form, on your
report, etc. You seem to be under the impression that just because a
value is displayed in your work orders form, it has to be stored in
your work orders table. That is not the case. It's already stored in
your equipment table and you can retrieve it whenever you need to.

If I've completely misunderstood your DB structure or what you are
trying to do let me know.

HTH

 

[Beetle]

This is excatly my problem and why I think I need to store the data in the
workorders table. The Equipment table is updated weekly using an odbc driver
to J.D. Edwards. I have a txt file that is updated when a report in Edwards
is run (this txt file lives on a company server.) Then, I have a linked table
to that txt file & I have a make table query setup to replace the equipment
table with the data that is in the linked table. This keeps the information
up-to-date.

Unfortunately, I'm not savvy enough to figure out how to do an update /
append vrs. a make table query when there is no primary key in the linked
table. As I said in a previous post, the account number is listed as many
times as there is equipment on loan. There is no single field that's not
duplicated at some point. Even if I add a primary key to the table, it would
be blown away when I run the make table query.

First of all, thank you both for the time you've invested in this issue I'm
having...
pietlinden, I tried that - it doesn't carry over to the workorders table
like I thought it would.
Beetle, I really appreciate the time you took to explain bound vs. unbound.
I get heavily involved with access for a few months out of any given year and
then I don't touch it. Some of these concepts are lost during that interim
period.
Regarding your suggested solution, I think that is a complicated way to
carry data from a form to a table. Especially since my expertise with VBA is
pathetic to say the least.
Is this really so complicated? I want to carry a field to a table from a
form. The data is stored in two locations on the form. It seems the txt box
bound to the combo box is not going to work (for whatever reason.) But, as I
said, I am able to carry the make/model field (cbo) over to the workorders
table.
In the Row Source of my combo box, I have three columns:
0 - Account Number; with criteria to pull all equipment in the equipment
table with that account number.
1 - Equip Description
2 - Serial Number
If I can write the Equip Description field to the workorders table, can't I
write the serial number field as well? Or, should I give up on this and go
back to making the user manually type in the equipment description & serial
number?
:
Hello JK
To answer your question, yes there is a method of storing this type of data
in a table field. To use the column method like you are, the textbox needs to
be unbound. In other words it's not bound directly to a field in a table or
query. Thats why when you save your data, the value in the text box doesn't
save, because Access has no way of knowing where to save it. In order to save
the value in an unbound contol like your text box, you have to use code to
tell Access where to save it. The code might look something like;
Dim rst AS Recordset
set rst = CurrentDb.OpenRecordSet (Select * From tblWorkOrders Where
ProductID=" & Me![ProductID])
rst.AddNew
rst!SerialNumber = Me!SerialNumber
rst.Update
rst.Close
Set rst = Nothing
This is sort of the readers digest version (normally there would be a little
more to code like this). Basically this creates a recordset copy of your
table, inserts the value from your unbound text box to a field in the
recordset, then saves it to a field in your table.
However, I don't now if you could even use this method. This is typically
used in situations where you only need to save one field, or maybe where the
entire form and all of it's fields are UNBOUND. If the other fields on your
form are BOUND to a query or table field, then you would have a situation
where you are trying to save some fields directly while trying to save the
text box value indirectly (via code) all to the same record in your table. I
don't know if this is even possible.
Again, I will reiterate that it is not necessary to do this. If the serial
number ealready exists in one of your tables, then all you need to do is
*look it up* when you need it. There are different ways of doing this. You
can use the Column method (which you already are doing). You could also use
the DLookup function.There are probably other ways that aren't coming to mind
right now.
So basically, my advice is don't try to save the serial number to the
WorkOrders table. I wen't to the trouble of explaining all this because, if
you are anything like me , you don't just want someone to tell you how to do
something, you also want to know WHY (dammit)
HTH
--
_________
Sean Bailey
:
I understand, but if I want to report on the workorders, this becomes a
problem. A customer could easily have two identical coffee machines. The only
difference between the two would be the serial numbers. So, if I write up a
workorder for one of those machines, I would have no way of displaying the
serial number (unless I'm missing something?)
The amount of extra data stored in the database would be minimal. Is there
no way to carry this type of txt field over to a table?
:
I have a workorder database that I'm tying to improve.
Form: Workorders
Table: Workorders
In my form, I've created two combo box controls.
Make/Model(1)
Make/Model(2)
The combo box pull down menu displays the customers current list of loaned
equipment allowing the administrator to pick one.
Once a coffee machine is selected, I have the serial number automatically
populate a separate text box right next to the combo box
(=MakeAndModel1.Column(2))
The two serial number txt boxes are:
txtSN1
txtSN2
I'm trying to write these two fields to the workorders table along with both
make/model fields. The make/model fields are working, I'm struggling with the
serial number fields...
Thx in advance.
Presumably, if the serial number for a product appears in one of the
columns of you combo box query, then that serial number is already
stored in one of the tables in your DB. Your query had to get it from
somewhere (maybe not the Workorders table, but some other table). You
would not normally want to store the same serial number in two
different tables. Just have it appear on your Workorders form (which
it sounds like it is already doing).
HTH- Hide quoted text -
- Show quoted text -
If I can write the Equip Description field to the workorders table, can't I
write the serial number field as well? Or, should I give up on this and go
back to making the user manually type in the equipment description & serial
number?

You should do neither.

Here is what I've gathered about your DB from your posts so far (this
may not be exact, but hopefully it's close to what you have).

You have an Accounts table (or something similar) that stores
information about who you provide equipment to, a Workorders table,
and an Equipment table. You table structure might look something like;

tblAccounts
**************
AccountID (Primary Key)
AccountName
Address
Phone
Other fields related to the account

tblWorkOrders
*****************
WorkOrderID (PK)
AccountID (Foreign Key to the Accounts table)
EquipmentID (FK to the Equipment table)
OrderDate
Other fields related specifically to the workorder

tblEquipment
****************
EquipmentID
EquipmentDescription
SerialNumber
Other fields related to each piece of equipment

As far as your equipment goes, the only value that needs to be stored
in tblWorkOrders is the EquipmentID as a foreign key to tblEquipment.
There is absolutely no reason to store the description or the serial
number in tblWorkOrders. You only need to lookup those fields when you
need them. You just tell Access "show me the description and serial
number that match the EquipmentID for this work order". You do this
via some of the methods that we have already covered (Column method,
DLookup, etc.). The values then get displayed in your form, on your
report, etc. You seem to be under the impression that just because a
value is displayed in your work orders form, it has to be stored in
your work orders table. That is not the case. It's already stored in
your equipment table and you can retrieve it whenever you need to.

If I've completely misunderstood your DB structure or what you are
trying to do let me know.

HTH- Hide quoted text -

- Show quoted text -

Well that certainly is a new wrinkle. You hadn't previously mentioned
the issue with linking to externel data. I have a little experience
with this, but I am by no means an expert, and I would not want to
give you incorrect advice which may cause you more issues than you
already have.

I will say that if your DB is functioning as you described (with every
field being duplicated in multiple tables, tables with no PK, etc.)
then you have an inherent design problem. However, maybe you are in a
situation where you can't redesign the DB right now, so you might try
this;

I'm guessing that right now you have the following as the *control
source* of your text box;

(=MakeAndModel1.Column(2))

Remove that, and set the control source of the text box to the field
in your WorkOrders table.

Then, in the AfterUpdate event of your combo box put;

Me![NameOfYourTextbox] = Me![NameOfYourComboBox].Column(2)

Keep in mind that this will overwirite any existing value that is
already in that field (maybe that's OK with you, I don't know)

You might want to test it some just to make sure it's working like you
want, and not overwriting anything that you don't want it to.

You might be saying to yourself "That seems simple, why the *&%^"
didn't you just tell me that in the first place". That's because I'm
not in the habit of telling people how to do things that they normally
shouldn't be doing. This goes against the standard rules of good
database design, but in your case maybe it's the only way you can
approach it right now. Actually, what I hope is that maybe someone
with more expertise will pickup on this thread and offer some advice
on how to get it working correctly. In the meantime, hope this helps
and good luck.

 

[Guest]

I can't tell you how thankful I am!

I thought myself how to build access databases using the database we've been
discussing. It started out as a small simple db, but is now a rather large
complicated db. What I think I should do is spend some time researching good
db design (table structure, etc) and revamp my application (maybe even take
an Access Class or 2.)

You’ve got me thinking - for that, and for all of the work you've done to
assist me, I thank you.

Your last suggested solution will work perfectly for the time-being.
I will now figure out how to eliminate the duplication of data throughout my
entire application.

This is excatly my problem and why I think I need to store the data in the
workorders table. The Equipment table is updated weekly using an odbc driver
to J.D. Edwards. I have a txt file that is updated when a report in Edwards
is run (this txt file lives on a company server.) Then, I have a linked table
to that txt file & I have a make table query setup to replace the equipment
table with the data that is in the linked table. This keeps the information
up-to-date.

Unfortunately, I'm not savvy enough to figure out how to do an update /
append vrs. a make table query when there is no primary key in the linked
table. As I said in a previous post, the account number is listed as many
times as there is equipment on loan. There is no single field that's not
duplicated at some point. Even if I add a primary key to the table, it would
be blown away when I run the make table query.

First of all, thank you both for the time you've invested in this issue I'm
having...
pietlinden, I tried that - it doesn't carry over to the workorders table
like I thought it would.

Beetle, I really appreciate the time you took to explain bound vs. unbound.
I get heavily involved with access for a few months out of any given year and
then I don't touch it. Some of these concepts are lost during that interim
period.

Regarding your suggested solution, I think that is a complicated way to
carry data from a form to a table. Especially since my expertise with VBA is
pathetic to say the least.

Is this really so complicated? I want to carry a field to a table from a
form. The data is stored in two locations on the form. It seems the txt box
bound to the combo box is not going to work (for whatever reason.) But, as I
said, I am able to carry the make/model field (cbo) over to the workorders
table.

In the Row Source of my combo box, I have three columns:

0 - Account Number; with criteria to pull all equipment in the equipment
table with that account number.
1 - Equip Description
2 - Serial Number

If I can write the Equip Description field to the workorders table, can't I
write the serial number field as well? Or, should I give up on this and go
back to making the user manually type in the equipment description & serial
number?

:
Hello JK

To answer your question, yes there is a method of storing this type of data
in a table field. To use the column method like you are, the textbox needs to
be unbound. In other words it's not bound directly to a field in a table or
query. Thats why when you save your data, the value in the text box doesn't
save, because Access has no way of knowing where to save it. In order to save
the value in an unbound contol like your text box, you have to use code to
tell Access where to save it. The code might look something like;

Dim rst AS Recordset

set rst = CurrentDb.OpenRecordSet (Select * From tblWorkOrders Where
ProductID=" & Me![ProductID])

rst.AddNew

rst!SerialNumber = Me!SerialNumber

rst.Update
rst.Close

Set rst = Nothing

This is sort of the readers digest version (normally there would be a little
more to code like this). Basically this creates a recordset copy of your
table, inserts the value from your unbound text box to a field in the
recordset, then saves it to a field in your table.

However, I don't now if you could even use this method. This is typically
used in situations where you only need to save one field, or maybe where the
entire form and all of it's fields are UNBOUND. If the other fields on your
form are BOUND to a query or table field, then you would have a situation
where you are trying to save some fields directly while trying to save the
text box value indirectly (via code) all to the same record in your table. I
don't know if this is even possible.

Again, I will reiterate that it is not necessary to do this. If the serial
number ealready exists in one of your tables, then all you need to do is
*look it up* when you need it. There are different ways of doing this. You
can use the Column method (which you already are doing). You could also use
the DLookup function.There are probably other ways that aren't coming to mind
right now.

So basically, my advice is don't try to save the serial number to the
WorkOrders table. I wen't to the trouble of explaining all this because, if
you are anything like me , you don't just want someone to tell you how to do
something, you also want to know WHY (dammit)

Sean Bailey

"JK" wrote:

I understand, but if I want to report on the workorders, this becomes a
problem. A customer could easily have two identical coffee machines. The only
difference between the two would be the serial numbers. So, if I write up a
workorder for one of those machines, I would have no way of displaying the
serial number (unless I'm missing something?)

The amount of extra data stored in the database would be minimal. Is there
no way to carry this type of txt field over to a table?

"Beetle" wrote:

I have a workorder database that I'm tying to improve.

Form: Workorders
Table: Workorders

In my form, I've created two combo box controls.
Make/Model(1)
Make/Model(2)

The combo box pull down menu displays the customers current list of loaned
equipment allowing the administrator to pick one.

Once a coffee machine is selected, I have the serial number automatically
populate a separate text box right next to the combo box
(=MakeAndModel1.Column(2))

The two serial number txt boxes are:
txtSN1
txtSN2

I'm trying to write these two fields to the workorders table along with both
make/model fields. The make/model fields are working, I'm struggling with the
serial number fields...

Thx in advance.

Presumably, if the serial number for a product appears in one of the
columns of you combo box query, then that serial number is already
stored in one of the tables in your DB. Your query had to get it from
somewhere (maybe not the Workorders table, but some other table). You
would not normally want to store the same serial number in two
different tables. Just have it appear on your Workorders form (which
it sounds like it is already doing).

HTH- Hide quoted text -

- Show quoted text -

If I can write the Equip Description field to the workorders table, can't I
write the serial number field as well? Or, should I give up on this and go
back to making the user manually type in the equipment description & serial
number?

You should do neither.

Here is what I've gathered about your DB from your posts so far (this
may not be exact, but hopefully it's close to what you have).

You have an Accounts table (or something similar) that stores
information about who you provide equipment to, a Workorders table,
and an Equipment table. You table structure might look something like;

tblAccounts
**************
AccountID (Primary Key)
AccountName
Address
Phone
Other fields related to the account

tblWorkOrders
*****************
WorkOrderID (PK)
AccountID (Foreign Key to the Accounts table)
EquipmentID (FK to the Equipment table)
OrderDate
Other fields related specifically to the workorder

tblEquipment
****************
EquipmentID
EquipmentDescription
SerialNumber
Other fields related to each piece of equipment

As far as your equipment goes, the only value that needs to be stored
in tblWorkOrders is the EquipmentID as a foreign key to tblEquipment.
There is absolutely no reason to store the description or the serial
number in tblWorkOrders. You only need to lookup those fields when you
need them. You just tell Access "show me the description and serial
number that match the EquipmentID for this work order". You do this
via some of the methods that we have already covered (Column method,
DLookup, etc.). The values then get displayed in your form, on your
report, etc. You seem to be under the impression that just because a
value is displayed in your work orders form, it has to be stored in
your work orders table. That is not the case. It's already stored in
your equipment table and you can retrieve it whenever you need to.

If I've completely misunderstood your DB structure or what you are
trying to do let me know.

HTH- Hide quoted text -

- Show quoted text -

Well that certainly is a new wrinkle. You hadn't previously mentioned
the issue with linking to externel data. I have a little experience
with this, but I am by no means an expert, and I would not want to
give you incorrect advice which may cause you more issues than you
already have.

I will say that if your DB is functioning as you described (with every
field being duplicated in multiple tables, tables with no PK, etc.)
then you have an inherent design problem. However, maybe you are in a
situation where you can't redesign the DB right now, so you might try
this;

I'm guessing that right now you have the following as the *control
source* of your text box;

(=MakeAndModel1.Column(2))

Remove that, and set the control source of the text box to the field
in your WorkOrders table.

Then, in the AfterUpdate event of your combo box put;

Me![NameOfYourTextbox] = Me![NameOfYourComboBox].Column(2)

Keep in mind that this will overwirite any existing value that is
already in that field (maybe that's OK with you, I don't know)

You might want to test it some just to make sure it's working like you
want, and not overwriting anything that you don't want it to.

You might be saying to yourself "That seems simple, why the *&%^"
didn't you just tell me that in the first place". That's because I'm
not in the habit of telling people how to do things that they normally
shouldn't be doing. This goes against the standard rules of good
database design, but in your case maybe it's the only way you can
approach it right now. Actually, what I hope is that maybe someone
with more expertise will pickup on this thread and offer some advice
on how to get it working correctly. In the meantime, hope this helps
and good luck.

 

[derek wilkie]

I'm trying tofind a decent site to learn SQL database scriptwriting, any
idea's please.

I can't tell you how thankful I am!

I thought myself how to build access databases using the database we've
been
discussing. It started out as a small simple db, but is now a rather large
complicated db. What I think I should do is spend some time researching
good
db design (table structure, etc) and revamp my application (maybe even
take
an Access Class or 2.)

You've got me thinking - for that, and for all of the work you've done to
assist me, I thank you.

Your last suggested solution will work perfectly for the time-being.
I will now figure out how to eliminate the duplication of data throughout
my
entire application.

This is excatly my problem and why I think I need to store the data in
the
workorders table. The Equipment table is updated weekly using an odbc
driver
to J.D. Edwards. I have a txt file that is updated when a report in
Edwards
is run (this txt file lives on a company server.) Then, I have a linked
table
to that txt file & I have a make table query setup to replace the
equipment
table with the data that is in the linked table. This keeps the
information
up-to-date.

Unfortunately, I'm not savvy enough to figure out how to do an update /
append vrs. a make table query when there is no primary key in the
linked
table. As I said in a previous post, the account number is listed as
many
times as there is equipment on loan. There is no single field that's
not
duplicated at some point. Even if I add a primary key to the table, it
would
be blown away when I run the make table query.



:
First of all, thank you both for the time you've invested in this
issue I'm
having...
pietlinden, I tried that - it doesn't carry over to the workorders
table
like I thought it would.

Beetle, I really appreciate the time you took to explain bound vs.
unbound.
I get heavily involved with access for a few months out of any
given year and
then I don't touch it. Some of these concepts are lost during that
interim
period.

Regarding your suggested solution, I think that is a complicated
way to
carry data from a form to a table. Especially since my expertise
with VBA is
pathetic to say the least.

Is this really so complicated? I want to carry a field to a table
from a
form. The data is stored in two locations on the form. It seems the
txt box
bound to the combo box is not going to work (for whatever reason.)
But, as I
said, I am able to carry the make/model field (cbo) over to the
workorders
table.

In the Row Source of my combo box, I have three columns:

0 - Account Number; with criteria to pull all equipment in the
equipment
table with that account number.
1 - Equip Description
2 - Serial Number

If I can write the Equip Description field to the workorders table,
can't I
write the serial number field as well? Or, should I give up on this
and go
back to making the user manually type in the equipment description
& serial
number?

:
Hello JK

To answer your question, yes there is a method of storing this
type of data
in a table field. To use the column method like you are, the
textbox needs to
be unbound. In other words it's not bound directly to a field in
a table or
query. Thats why when you save your data, the value in the text
box doesn't
save, because Access has no way of knowing where to save it. In
order to save
the value in an unbound contol like your text box, you have to
use code to
tell Access where to save it. The code might look something like;

Dim rst AS Recordset

set rst = CurrentDb.OpenRecordSet (Select * From tblWorkOrders
Where
ProductID=" & Me![ProductID])

rst.AddNew

rst!SerialNumber = Me!SerialNumber

rst.Update
rst.Close

Set rst = Nothing

This is sort of the readers digest version (normally there would
be a little
more to code like this). Basically this creates a recordset copy
of your
table, inserts the value from your unbound text box to a field in
the
recordset, then saves it to a field in your table.

However, I don't now if you could even use this method. This is
typically
used in situations where you only need to save one field, or
maybe where the
entire form and all of it's fields are UNBOUND. If the other
fields on your
form are BOUND to a query or table field, then you would have a
situation
where you are trying to save some fields directly while trying to
save the
text box value indirectly (via code) all to the same record in
your table. I
don't know if this is even possible.

Again, I will reiterate that it is not necessary to do this. If
the serial
number ealready exists in one of your tables, then all you need
to do is
*look it up* when you need it. There are different ways of doing
this. You
can use the Column method (which you already are doing). You
could also use
the DLookup function.There are probably other ways that aren't
coming to mind
right now.

So basically, my advice is don't try to save the serial number to
the
WorkOrders table. I wen't to the trouble of explaining all this
because, if
you are anything like me , you don't just want someone to tell
you how to do
something, you also want to know WHY (dammit)

HTH
--
_________

Sean Bailey

:

I understand, but if I want to report on the workorders, this
becomes a
problem. A customer could easily have two identical coffee
machines. The only
difference between the two would be the serial numbers. So, if
I write up a
workorder for one of those machines, I would have no way of
displaying the
serial number (unless I'm missing something?)

The amount of extra data stored in the database would be
minimal. Is there
no way to carry this type of txt field over to a table?

:

On Oct 11, 3:23 pm, JK <[email protected]>
wrote:
I have a workorder database that I'm tying to improve.

Form: Workorders
Table: Workorders

In my form, I've created two combo box controls.
Make/Model(1)
Make/Model(2)

The combo box pull down menu displays the customers current
list of loaned
equipment allowing the administrator to pick one.

Once a coffee machine is selected, I have the serial number
automatically
populate a separate text box right next to the combo box
(=MakeAndModel1.Column(2))

The two serial number txt boxes are:
txtSN1
txtSN2

I'm trying to write these two fields to the workorders
table along with both
make/model fields. The make/model fields are working, I'm
struggling with the
serial number fields...

Thx in advance.

Presumably, if the serial number for a product appears in one
of the
columns of you combo box query, then that serial number is
already
stored in one of the tables in your DB. Your query had to get
it from
somewhere (maybe not the Workorders table, but some other
table). You
would not normally want to store the same serial number in
two
different tables. Just have it appear on your Workorders form
(which
it sounds like it is already doing).

HTH- Hide quoted text -

- Show quoted text -

If I can write the Equip Description field to the workorders table,
can't I
write the serial number field as well? Or, should I give up on this
and go
back to making the user manually type in the equipment description
& serial
number?

You should do neither.

Here is what I've gathered about your DB from your posts so far (this
may not be exact, but hopefully it's close to what you have).

You have an Accounts table (or something similar) that stores
information about who you provide equipment to, a Workorders table,
and an Equipment table. You table structure might look something
like;

tblAccounts
**************
AccountID (Primary Key)
AccountName
Address
Phone
Other fields related to the account

tblWorkOrders
*****************
WorkOrderID (PK)
AccountID (Foreign Key to the Accounts table)
EquipmentID (FK to the Equipment table)
OrderDate
Other fields related specifically to the workorder

tblEquipment
****************
EquipmentID
EquipmentDescription
SerialNumber
Other fields related to each piece of equipment

As far as your equipment goes, the only value that needs to be stored
in tblWorkOrders is the EquipmentID as a foreign key to tblEquipment.
There is absolutely no reason to store the description or the serial
number in tblWorkOrders. You only need to lookup those fields when
you
need them. You just tell Access "show me the description and serial
number that match the EquipmentID for this work order". You do this
via some of the methods that we have already covered (Column method,
DLookup, etc.). The values then get displayed in your form, on your
report, etc. You seem to be under the impression that just because a
value is displayed in your work orders form, it has to be stored in
your work orders table. That is not the case. It's already stored in
your equipment table and you can retrieve it whenever you need to.

If I've completely misunderstood your DB structure or what you are
trying to do let me know.

HTH- Hide quoted text -

- Show quoted text -

Well that certainly is a new wrinkle. You hadn't previously mentioned
the issue with linking to externel data. I have a little experience
with this, but I am by no means an expert, and I would not want to
give you incorrect advice which may cause you more issues than you
already have.

I will say that if your DB is functioning as you described (with every
field being duplicated in multiple tables, tables with no PK, etc.)
then you have an inherent design problem. However, maybe you are in a
situation where you can't redesign the DB right now, so you might try
this;

I'm guessing that right now you have the following as the *control
source* of your text box;

(=MakeAndModel1.Column(2))

Remove that, and set the control source of the text box to the field
in your WorkOrders table.

Then, in the AfterUpdate event of your combo box put;

Me![NameOfYourTextbox] = Me![NameOfYourComboBox].Column(2)

Keep in mind that this will overwirite any existing value that is
already in that field (maybe that's OK with you, I don't know)

You might want to test it some just to make sure it's working like you
want, and not overwriting anything that you don't want it to.

You might be saying to yourself "That seems simple, why the *&%^"
didn't you just tell me that in the first place". That's because I'm
not in the habit of telling people how to do things that they normally
shouldn't be doing. This goes against the standard rules of good
database design, but in your case maybe it's the only way you can
approach it right now. Actually, what I hope is that maybe someone
with more expertise will pickup on this thread and offer some advice
on how to get it working correctly. In the meantime, hope this helps
and good luck.