A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Update Tables using forms



 
 
Thread Tools Display Modes
  #1  
Old January 24th, 2005, 09:56 PM
achett
external usenet poster
 
Posts: n/a
Default Update Tables using forms

I have posted my question 3 times already, and I haven't gotten any help..If
you come across my questions please help.
I have a Table with the following fields JobName, Job Title,
Printer, Lines..etc. I would like to enter and update this table via a
Form.On the Form I have a combo Box(JobName), which helps me to retrieve a
record from the table. Once thats done, I want to assign a number to that
record (I might use the same JobName more than Once with a different Job
number). I do not have the Job Number included in the Table and I do not have
any Primary keys. After I make the appropriate changes to the record I want
that record to be saved to the Table.

Thanks a lot..


  #2  
Old January 25th, 2005, 03:09 AM
accessjunky
external usenet poster
 
Posts: n/a
Default

Hi achett,

When creating a form using the wizard process you are prompted to select the
table or query (the record source) that the form is based on. The form is
then linked to the table or query and so the data entered into the form is
actually being entered into the table. The form itself doesn't hold data, it
merely presents the data from the table or query that it is linked to. The
changes are automatically saved when moving to a different record.

If you don't have the required fields in the table or query that is the
form's record source then you have nowhere to store the data that you enter
via the form.

However, looking up a record using the combo box (creating using the wizard
to 'find a record on my form...' I would guess) will open the record with
that JobName. If you have the same JobName many times then the combo box will
have the jobname repeated and, without further information when selecting in
the comb box, it will not be easy to select the correct jobname record.

If JobNumber is unique then it would be possible to use that as aprimary key
but you would be better served to create a field in the table, perhaps called
JobID, with data type AutoNumber. Recreatint the lookup combo box using the
Wizard could then find the record based on its JobID but, if you follow the
wizard, the user will see the JobName. I would suggest adding the JobNumber
to the lookup combo box also so that the user can see which JobName is
required (e.g. if it's blank that may be the one that's required.

Hope this help

accessjunky

"achett" wrote:

I have posted my question 3 times already, and I haven't gotten any help..If
you come across my questions please help.
I have a Table with the following fields JobName, Job Title,
Printer, Lines..etc. I would like to enter and update this table via a
Form.On the Form I have a combo Box(JobName), which helps me to retrieve a
record from the table. Once thats done, I want to assign a number to that
record (I might use the same JobName more than Once with a different Job
number). I do not have the Job Number included in the Table and I do not have
any Primary keys. After I make the appropriate changes to the record I want
that record to be saved to the Table.

Thanks a lot..


  #3  
Old January 25th, 2005, 04:47 PM
achett
external usenet poster
 
Posts: n/a
Default

Hi accessjunky,
Thanks for your help. I'am trying to creat a weekly Job schedule where I
will have the JobName, Job Title and 120 other parameters. I have 25 jobs in
total and each job has 5 parameters which I would like to change accordingly.
I also would like to assign a Job number as I make changes to the record. I
might have to use the same jobName more than once with a different Jobnumber.
At this point I have the table the form all working except that when I make
changes to a record, it does not add that as a new record to the table but
simply changes the existing record. Hope I did not confuse you. Any help is
greatly appreciated.

achett


"accessjunky" wrote:

Hi achett,

When creating a form using the wizard process you are prompted to select the
table or query (the record source) that the form is based on. The form is
then linked to the table or query and so the data entered into the form is
actually being entered into the table. The form itself doesn't hold data, it
merely presents the data from the table or query that it is linked to. The
changes are automatically saved when moving to a different record.

If you don't have the required fields in the table or query that is the
form's record source then you have nowhere to store the data that you enter
via the form.

However, looking up a record using the combo box (creating using the wizard
to 'find a record on my form...' I would guess) will open the record with
that JobName. If you have the same JobName many times then the combo box will
have the jobname repeated and, without further information when selecting in
the comb box, it will not be easy to select the correct jobname record.

If JobNumber is unique then it would be possible to use that as aprimary key
but you would be better served to create a field in the table, perhaps called
JobID, with data type AutoNumber. Recreatint the lookup combo box using the
Wizard could then find the record based on its JobID but, if you follow the
wizard, the user will see the JobName. I would suggest adding the JobNumber
to the lookup combo box also so that the user can see which JobName is
required (e.g. if it's blank that may be the one that's required.

Hope this help

accessjunky

"achett" wrote:

I have posted my question 3 times already, and I haven't gotten any help..If
you come across my questions please help.
I have a Table with the following fields JobName, Job Title,
Printer, Lines..etc. I would like to enter and update this table via a
Form.On the Form I have a combo Box(JobName), which helps me to retrieve a
record from the table. Once thats done, I want to assign a number to that
record (I might use the same JobName more than Once with a different Job
number). I do not have the Job Number included in the Table and I do not have
any Primary keys. After I make the appropriate changes to the record I want
that record to be saved to the Table.

Thanks a lot..


  #4  
Old January 26th, 2005, 12:57 AM
accessjunky
external usenet poster
 
Posts: n/a
Default

There are two distinct sets of data here - A look up table that holds the
JobName, JobTitle and all other values that are related to the JobName. These
records are not an individual Job on a particular date but define different
types of Job. If you haven't already then I would ensure that the table
containing JobName etc. has an Autonumber Primary Key field (e.g. JobTypeID)
- the table could be named JobTypes.

The second set of data, held in a table called e.g. JobEvents, relates to
individual occurrences of the JobName, probably related to a particular date
and possibly time. This table should have an AutoNumber Primary Key field
(e.g. JobEventID - the names chosen easily distinguish the two sets of data).

There should also be a field in the JobEvents table named JobTypeID (or the
same as the Primary Key in JobTypes above). This field will be a Number data
type and should be a Long Integer size (the default for a Number field). This
field will be a link to the JobTypes table. You may change the data type of
this field in the JobEvents table to 'LookUp Wizard' to link it to the same
named field in the JobEvents table (although this is not always recommended
it is easier to do this rather than set up the resulting combo box that is
created).

Next create a query based on both tables which should automatically link on
the JobTypeID fields in each table, especially if you use the Lookup Wizard
because it creates a relationship between the two fields.

Select the fields that will be required on the form. Ensure that you only
choose the JobTypeID field from the JobEvents table and not the (Primary Key)
field from the JobTypes table. When clicking on the combo box attached to the
JobTypeID in the form you actually want to populate the value in the
JobEvents table and so that's the one you need - you can't edit JobTypeID in
the JobTypes table because it is an Autonumber so don't select it for the
query to avoid confusion when creating the form.

Next: Create a form based on the query just created. Add the required fields
but, very important this bit, make sure that any field controls from the
JobTypes table are locked. The only purpose of those field in the form is to
show the related values for this JobType and not to edit them. Editing those
fields will change the values in all records with that JobType. Any fields
that change on each occurrence of the JobType should be in the JobEvent table.

When creating a new record in the form, the table being populated should be
the JobEvents table. Selecting the JobName in the JobTypeID combo box should
automatically show all of the related information for that JobName
(effectively you are selecting the JobTypeID but if you used the Lookup
Wizard in the table design above you will be prompted to hide the key column
and as a result see the second column which should be the JobName).

I know there's a lot here but hopefully this will give you something to make
progress with.

Good Luck

accessjunky




"achett" wrote:

Hi accessjunky,
Thanks for your help. I'am trying to creat a weekly Job schedule where I
will have the JobName, Job Title and 120 other parameters. I have 25 jobs in
total and each job has 5 parameters which I would like to change accordingly.
I also would like to assign a Job number as I make changes to the record. I
might have to use the same jobName more than once with a different Jobnumber.
At this point I have the table the form all working except that when I make
changes to a record, it does not add that as a new record to the table but
simply changes the existing record. Hope I did not confuse you. Any help is
greatly appreciated.

achett


"accessjunky" wrote:

Hi achett,

When creating a form using the wizard process you are prompted to select the
table or query (the record source) that the form is based on. The form is
then linked to the table or query and so the data entered into the form is
actually being entered into the table. The form itself doesn't hold data, it
merely presents the data from the table or query that it is linked to. The
changes are automatically saved when moving to a different record.

If you don't have the required fields in the table or query that is the
form's record source then you have nowhere to store the data that you enter
via the form.

However, looking up a record using the combo box (creating using the wizard
to 'find a record on my form...' I would guess) will open the record with
that JobName. If you have the same JobName many times then the combo box will
have the jobname repeated and, without further information when selecting in
the comb box, it will not be easy to select the correct jobname record.

If JobNumber is unique then it would be possible to use that as aprimary key
but you would be better served to create a field in the table, perhaps called
JobID, with data type AutoNumber. Recreatint the lookup combo box using the
Wizard could then find the record based on its JobID but, if you follow the
wizard, the user will see the JobName. I would suggest adding the JobNumber
to the lookup combo box also so that the user can see which JobName is
required (e.g. if it's blank that may be the one that's required.

Hope this help

accessjunky

"achett" wrote:

I have posted my question 3 times already, and I haven't gotten any help..If
you come across my questions please help.
I have a Table with the following fields JobName, Job Title,
Printer, Lines..etc. I would like to enter and update this table via a
Form.On the Form I have a combo Box(JobName), which helps me to retrieve a
record from the table. Once thats done, I want to assign a number to that
record (I might use the same JobName more than Once with a different Job
number). I do not have the Job Number included in the Table and I do not have
any Primary keys. After I make the appropriate changes to the record I want
that record to be saved to the Table.

Thanks a lot..


  #5  
Old January 28th, 2005, 12:13 AM
achett
external usenet poster
 
Posts: n/a
Default

Hello accessjunky,
Thanks alot for your help..I haven't had time to work on this yet. but will
Let you know how it all turns out. Thanks alot again.

achett

"accessjunky" wrote:

There are two distinct sets of data here - A look up table that holds the
JobName, JobTitle and all other values that are related to the JobName. These
records are not an individual Job on a particular date but define different
types of Job. If you haven't already then I would ensure that the table
containing JobName etc. has an Autonumber Primary Key field (e.g. JobTypeID)
- the table could be named JobTypes.

The second set of data, held in a table called e.g. JobEvents, relates to
individual occurrences of the JobName, probably related to a particular date
and possibly time. This table should have an AutoNumber Primary Key field
(e.g. JobEventID - the names chosen easily distinguish the two sets of data).

There should also be a field in the JobEvents table named JobTypeID (or the
same as the Primary Key in JobTypes above). This field will be a Number data
type and should be a Long Integer size (the default for a Number field). This
field will be a link to the JobTypes table. You may change the data type of
this field in the JobEvents table to 'LookUp Wizard' to link it to the same
named field in the JobEvents table (although this is not always recommended
it is easier to do this rather than set up the resulting combo box that is
created).

Next create a query based on both tables which should automatically link on
the JobTypeID fields in each table, especially if you use the Lookup Wizard
because it creates a relationship between the two fields.

Select the fields that will be required on the form. Ensure that you only
choose the JobTypeID field from the JobEvents table and not the (Primary Key)
field from the JobTypes table. When clicking on the combo box attached to the
JobTypeID in the form you actually want to populate the value in the
JobEvents table and so that's the one you need - you can't edit JobTypeID in
the JobTypes table because it is an Autonumber so don't select it for the
query to avoid confusion when creating the form.

Next: Create a form based on the query just created. Add the required fields
but, very important this bit, make sure that any field controls from the
JobTypes table are locked. The only purpose of those field in the form is to
show the related values for this JobType and not to edit them. Editing those
fields will change the values in all records with that JobType. Any fields
that change on each occurrence of the JobType should be in the JobEvent table.

When creating a new record in the form, the table being populated should be
the JobEvents table. Selecting the JobName in the JobTypeID combo box should
automatically show all of the related information for that JobName
(effectively you are selecting the JobTypeID but if you used the Lookup
Wizard in the table design above you will be prompted to hide the key column
and as a result see the second column which should be the JobName).

I know there's a lot here but hopefully this will give you something to make
progress with.

Good Luck

accessjunky




"achett" wrote:

Hi accessjunky,
Thanks for your help. I'am trying to creat a weekly Job schedule where I
will have the JobName, Job Title and 120 other parameters. I have 25 jobs in
total and each job has 5 parameters which I would like to change accordingly.
I also would like to assign a Job number as I make changes to the record. I
might have to use the same jobName more than once with a different Jobnumber.
At this point I have the table the form all working except that when I make
changes to a record, it does not add that as a new record to the table but
simply changes the existing record. Hope I did not confuse you. Any help is
greatly appreciated.

achett


"accessjunky" wrote:

Hi achett,

When creating a form using the wizard process you are prompted to select the
table or query (the record source) that the form is based on. The form is
then linked to the table or query and so the data entered into the form is
actually being entered into the table. The form itself doesn't hold data, it
merely presents the data from the table or query that it is linked to. The
changes are automatically saved when moving to a different record.

If you don't have the required fields in the table or query that is the
form's record source then you have nowhere to store the data that you enter
via the form.

However, looking up a record using the combo box (creating using the wizard
to 'find a record on my form...' I would guess) will open the record with
that JobName. If you have the same JobName many times then the combo box will
have the jobname repeated and, without further information when selecting in
the comb box, it will not be easy to select the correct jobname record.

If JobNumber is unique then it would be possible to use that as aprimary key
but you would be better served to create a field in the table, perhaps called
JobID, with data type AutoNumber. Recreatint the lookup combo box using the
Wizard could then find the record based on its JobID but, if you follow the
wizard, the user will see the JobName. I would suggest adding the JobNumber
to the lookup combo box also so that the user can see which JobName is
required (e.g. if it's blank that may be the one that's required.

Hope this help

accessjunky

"achett" wrote:

I have posted my question 3 times already, and I haven't gotten any help..If
you come across my questions please help.
I have a Table with the following fields JobName, Job Title,
Printer, Lines..etc. I would like to enter and update this table via a
Form.On the Form I have a combo Box(JobName), which helps me to retrieve a
record from the table. Once thats done, I want to assign a number to that
record (I might use the same JobName more than Once with a different Job
number). I do not have the Job Number included in the Table and I do not have
any Primary keys. After I make the appropriate changes to the record I want
that record to be saved to the Table.

Thanks a lot..


  #6  
Old January 28th, 2005, 01:25 AM
achett
external usenet poster
 
Posts: n/a
Default

Hi accessjunky,
Everything seems to work the way I want it to. There is only one problem,
when I change the Job Type ID and edit the parameters of a record it does not
add it to the table as a new record,but overwrites the old one. Can you
please advise. Thanks in advance....

achett

achett" wrote:

Hello accessjunky,
Thanks alot for your help..I haven't had time to work on this yet. but will
Let you know how it all turns out. Thanks alot again.

achett

"accessjunky" wrote:

There are two distinct sets of data here - A look up table that holds the
JobName, JobTitle and all other values that are related to the JobName. These
records are not an individual Job on a particular date but define different
types of Job. If you haven't already then I would ensure that the table
containing JobName etc. has an Autonumber Primary Key field (e.g. JobTypeID)
- the table could be named JobTypes.

The second set of data, held in a table called e.g. JobEvents, relates to
individual occurrences of the JobName, probably related to a particular date
and possibly time. This table should have an AutoNumber Primary Key field
(e.g. JobEventID - the names chosen easily distinguish the two sets of data).

There should also be a field in the JobEvents table named JobTypeID (or the
same as the Primary Key in JobTypes above). This field will be a Number data
type and should be a Long Integer size (the default for a Number field). This
field will be a link to the JobTypes table. You may change the data type of
this field in the JobEvents table to 'LookUp Wizard' to link it to the same
named field in the JobEvents table (although this is not always recommended
it is easier to do this rather than set up the resulting combo box that is
created).

Next create a query based on both tables which should automatically link on
the JobTypeID fields in each table, especially if you use the Lookup Wizard
because it creates a relationship between the two fields.

Select the fields that will be required on the form. Ensure that you only
choose the JobTypeID field from the JobEvents table and not the (Primary Key)
field from the JobTypes table. When clicking on the combo box attached to the
JobTypeID in the form you actually want to populate the value in the
JobEvents table and so that's the one you need - you can't edit JobTypeID in
the JobTypes table because it is an Autonumber so don't select it for the
query to avoid confusion when creating the form.

Next: Create a form based on the query just created. Add the required fields
but, very important this bit, make sure that any field controls from the
JobTypes table are locked. The only purpose of those field in the form is to
show the related values for this JobType and not to edit them. Editing those
fields will change the values in all records with that JobType. Any fields
that change on each occurrence of the JobType should be in the JobEvent table.

When creating a new record in the form, the table being populated should be
the JobEvents table. Selecting the JobName in the JobTypeID combo box should
automatically show all of the related information for that JobName
(effectively you are selecting the JobTypeID but if you used the Lookup
Wizard in the table design above you will be prompted to hide the key column
and as a result see the second column which should be the JobName).

I know there's a lot here but hopefully this will give you something to make
progress with.

Good Luck

accessjunky




"achett" wrote:

Hi accessjunky,
Thanks for your help. I'am trying to creat a weekly Job schedule where I
will have the JobName, Job Title and 120 other parameters. I have 25 jobs in
total and each job has 5 parameters which I would like to change accordingly.
I also would like to assign a Job number as I make changes to the record. I
might have to use the same jobName more than once with a different Jobnumber.
At this point I have the table the form all working except that when I make
changes to a record, it does not add that as a new record to the table but
simply changes the existing record. Hope I did not confuse you. Any help is
greatly appreciated.

achett


"accessjunky" wrote:

Hi achett,

When creating a form using the wizard process you are prompted to select the
table or query (the record source) that the form is based on. The form is
then linked to the table or query and so the data entered into the form is
actually being entered into the table. The form itself doesn't hold data, it
merely presents the data from the table or query that it is linked to. The
changes are automatically saved when moving to a different record.

If you don't have the required fields in the table or query that is the
form's record source then you have nowhere to store the data that you enter
via the form.

However, looking up a record using the combo box (creating using the wizard
to 'find a record on my form...' I would guess) will open the record with
that JobName. If you have the same JobName many times then the combo box will
have the jobname repeated and, without further information when selecting in
the comb box, it will not be easy to select the correct jobname record.

If JobNumber is unique then it would be possible to use that as aprimary key
but you would be better served to create a field in the table, perhaps called
JobID, with data type AutoNumber. Recreatint the lookup combo box using the
Wizard could then find the record based on its JobID but, if you follow the
wizard, the user will see the JobName. I would suggest adding the JobNumber
to the lookup combo box also so that the user can see which JobName is
required (e.g. if it's blank that may be the one that's required.

Hope this help

accessjunky

"achett" wrote:

I have posted my question 3 times already, and I haven't gotten any help..If
you come across my questions please help.
I have a Table with the following fields JobName, Job Title,
Printer, Lines..etc. I would like to enter and update this table via a
Form.On the Form I have a combo Box(JobName), which helps me to retrieve a
record from the table. Once thats done, I want to assign a number to that
record (I might use the same JobName more than Once with a different Job
number). I do not have the Job Number included in the Table and I do not have
any Primary keys. After I make the appropriate changes to the record I want
that record to be saved to the Table.

Thanks a lot..


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Tables and Forms. Farooq Sheri General Discussion 1 October 5th, 2004 11:36 AM
Variables, Forms, Tables Bryan Hughes General Discussion 1 September 25th, 2004 06:00 AM
Query-based forms with multiple tables Andrew Parker Using Forms 1 August 18th, 2004 01:49 AM
Update Presentation with Excel charts and tables Holly Powerpoint 2 August 11th, 2004 06:03 PM
Passing Variables or Database Parameters to forms and tables... R Schlageter New Users 1 May 12th, 2004 10:10 PM


All times are GMT +1. The time now is 09:53 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.