Update Tables using forms

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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..
 
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
 
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
 
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
 
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
 
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
 
Back
Top