Form Query

[chett via AccessMonster.com]

Hi!
Sorry the earlier post did not align the tables info right, so had to post it
again. I have been trying to build an automated schedule using tables and
forms.This is how they are laid out.

Table 1 (updatable)
Job Event:
Job Event Id (Primary Key)
JobType ID (Foreign Key)
JobName
Job Parameter Notes1
Job Parameter Value1

Table 2 (Cannot be updated)
Job Type:
JobEvent ID (Primary Key)
Job Name
Job Parameter Number1
Job Parameter Default Number
Job Parameter Description1
Job Number (Integer) (not sure if I need this field to save a job more than
once)

Form
Weekly Processing Schedule:
I have a combobox "Job Type ID" calling the Job which fills in the other
fields on the form. The problem is I would like to use a particular job from
the combobox(listed only once) more than once and change the parameter value
and save the record with a different Number. Please help as this is very
important.

Thanks in advance
Chett

 

[Guest]

Hi Chett,

I'm not sure I se a problem here - let's be sure I've got the picture right!

Table2 is linked via JobID to JobTypeID in Table1: two questions:
(1) - have you set up the 'relationships' and set up the links as
one-to-many?
(2) - did you check/tick the box 'Enforce Referential Integrity'?

I also assume the 'parameter value' you intend changing each time you use a
particular 'job type' is a value in table 1.

Therefore what I assume will happen is:
(1) - you want to create a new record, so you call whatever event is
necessary to do this (maybe a command button, maybe just using the navigation
buttons, as you prefer!), and you get a blank form.
(2) - whichever data you enter forces a new record on table1, and thus sets
its keyID (I assume you're using an autonumber? occasionally there are good
reasons not to, but usually autonumbers are fine).
(3) - you can select the 'job type' from your combo. It doesn't matter
whether that job has been selected before, for other records in Table1. And
it doesn't matter what data is in the other 'table1' fields.
(4) - therefore, you have a uniquely numbered 'job event' record - ie the
keyID for table 1.

I hope that's clear; please let me know if it was helpful, but also please
let me know if I've either misunderstood your problem or not been clear
enough in my reply!

All the best from England

 

[chett via AccessMonster.com]

Hi Doogle,
Thanks a lot for the info. I have been able to add a new record by creating
one to many-relationship, but there are a couple of more problems.
I also assume the 'parameter value' you intend changing each time you use a

particular 'job type' is a value in table 1. (**Your assumption is right, but there is a problem populating this field )

The code I'm using is to populate the Parameter Value field is:
=JobType ID.Column(12) (**number relevent to it's field)

Thanks Again
chett

Hi Chett,

I'm not sure I se a problem here - let's be sure I've got the picture right!

Table2 is linked via JobID to JobTypeID in Table1: two questions:
(1) - have you set up the 'relationships' and set up the links as
one-to-many?
(2) - did you check/tick the box 'Enforce Referential Integrity'?

I also assume the 'parameter value' you intend changing each time you use a
particular 'job type' is a value in table 1.

Therefore what I assume will happen is:
(1) - you want to create a new record, so you call whatever event is
necessary to do this (maybe a command button, maybe just using the navigation
buttons, as you prefer!), and you get a blank form.
(2) - whichever data you enter forces a new record on table1, and thus sets
its keyID (I assume you're using an autonumber? occasionally there are good
reasons not to, but usually autonumbers are fine).
(3) - you can select the 'job type' from your combo. It doesn't matter
whether that job has been selected before, for other records in Table1. And
it doesn't matter what data is in the other 'table1' fields.
(4) - therefore, you have a uniquely numbered 'job event' record - ie the
keyID for table 1.

I hope that's clear; please let me know if it was helpful, but also please
let me know if I've either misunderstood your problem or not been clear
enough in my reply!

All the best from England

Hi!
Sorry the earlier post did not align the tables info right, so had to post it

[quoted text clipped - 29 lines]

Thanks in advance
Chett

 

[Guest]

Hi Chett,

Sorry, you've just lost me!

Firstly, referencing a column in the way you've done is a ref to a column in
a combo box, not a column (ie a field) in a query or table. Are you really
trying to refer to the 13th column (yes, the numbering starts at 0, just to
confuse us...!!) in a combo, or acytually to a field in a query/table?

The correct syntax for a ref. to a combo would be of the form:
=forms!frmFormname!cboComboName.Column(12)

Is there any chance you could email me a copy of the relevant form(s),
query(ies) & table(s) (OK without data!!) so I have a clearer idea of what
you're trying to do? I'm pretty sure the solution is not difficult but I
can't quite see the 'goal'. (You could create a new blank database then
'import' the relevant objects into it).

My email address sounds like:

data dot dynamics at virgin dot net

- just type it into the email as it sounds, not as it reads, if you get me
(for example, the 'virgin dot net' bit would be written as 'virgin.net'.
OK, I'm stating the obvious. My apologies!!)

All the best


---
Laury Burr a.k.a. Doogle

Hi Doogle,
Thanks a lot for the info. I have been able to add a new record by creating
one to many-relationship, but there are a couple of more problems.
I also assume the 'parameter value' you intend changing each time you use a

particular 'job type' is a value in table 1. (**Your assumption is right, but there is a problem populating this field )

The code I'm using is to populate the Parameter Value field is:
=JobType ID.Column(12) (**number relevent to it's field)

Thanks Again
chett

Hi Chett,

I'm not sure I se a problem here - let's be sure I've got the picture right!

Table2 is linked via JobID to JobTypeID in Table1: two questions:
(1) - have you set up the 'relationships' and set up the links as
one-to-many?
(2) - did you check/tick the box 'Enforce Referential Integrity'?

I also assume the 'parameter value' you intend changing each time you use a
particular 'job type' is a value in table 1.

Therefore what I assume will happen is:
(1) - you want to create a new record, so you call whatever event is
necessary to do this (maybe a command button, maybe just using the navigation
buttons, as you prefer!), and you get a blank form.
(2) - whichever data you enter forces a new record on table1, and thus sets
its keyID (I assume you're using an autonumber? occasionally there are good
reasons not to, but usually autonumbers are fine).
(3) - you can select the 'job type' from your combo. It doesn't matter
whether that job has been selected before, for other records in Table1. And
it doesn't matter what data is in the other 'table1' fields.
(4) - therefore, you have a uniquely numbered 'job event' record - ie the
keyID for table 1.

I hope that's clear; please let me know if it was helpful, but also please
let me know if I've either misunderstood your problem or not been clear
enough in my reply!

All the best from England

Hi!
Sorry the earlier post did not align the tables info right, so had to post it

[quoted text clipped - 29 lines]

Thanks in advance
Chett

 

[chett via AccessMonster.com]

Hi Doogle,
The file is sent. Yes, I'm refering to the 13th column of the Query.

Thanks again
Chett

Hi Chett,

Sorry, you've just lost me!

Firstly, referencing a column in the way you've done is a ref to a column in
a combo box, not a column (ie a field) in a query or table. Are you really
trying to refer to the 13th column (yes, the numbering starts at 0, just to
confuse us...!!) in a combo, or acytually to a field in a query/table?

The correct syntax for a ref. to a combo would be of the form:
=forms!frmFormname!cboComboName.Column(12)

Is there any chance you could email me a copy of the relevant form(s),
query(ies) & table(s) (OK without data!!) so I have a clearer idea of what
you're trying to do? I'm pretty sure the solution is not difficult but I
can't quite see the 'goal'. (You could create a new blank database then
'import' the relevant objects into it).

My email address sounds like:

data dot dynamics at virgin dot net

- just type it into the email as it sounds, not as it reads, if you get me
(for example, the 'virgin dot net' bit would be written as 'virgin.net'.
OK, I'm stating the obvious. My apologies!!)

All the best

---
Laury Burr a.k.a. Doogle

Hi Doogle,
Thanks a lot for the info. I have been able to add a new record by creating

[quoted text clipped - 43 lines]

 

[chett via AccessMonster.com]

Hi Doogle,
I hope you are still there.

Chett

Hi Chett,

Sorry, you've just lost me!

Firstly, referencing a column in the way you've done is a ref to a column in
a combo box, not a column (ie a field) in a query or table. Are you really
trying to refer to the 13th column (yes, the numbering starts at 0, just to
confuse us...!!) in a combo, or acytually to a field in a query/table?

The correct syntax for a ref. to a combo would be of the form:
=forms!frmFormname!cboComboName.Column(12)

Is there any chance you could email me a copy of the relevant form(s),
query(ies) & table(s) (OK without data!!) so I have a clearer idea of what
you're trying to do? I'm pretty sure the solution is not difficult but I
can't quite see the 'goal'. (You could create a new blank database then
'import' the relevant objects into it).

My email address sounds like:

data dot dynamics at virgin dot net

- just type it into the email as it sounds, not as it reads, if you get me
(for example, the 'virgin dot net' bit would be written as 'virgin.net'.
OK, I'm stating the obvious. My apologies!!)

All the best

---
Laury Burr a.k.a. Doogle

Hi Doogle,
Thanks a lot for the info. I have been able to add a new record by creating

[quoted text clipped - 43 lines]