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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 |