Unbound Data Entry Form
in [Tables]
|
Prev: Lookup...
Next: Data Entry
From: Steve Muir on 5 Feb 2010 11:01 Bit of a "noob" question but here goes.... I am using an unbound form for data entry purposes. I am using an unbound form as I have some VBA code which checks that mandatory fields are filled in first. This works fine where all the fields are text boxes (i.e. not combos or list boxes) but the problem I'm having is that the parent table "tblContracts" has 4 lookup tables (which are used for combos with various fields on the form) and these are numeric fields which link between the tables (i.e. In the parent table the field would be SupplierID whick links to the child table SupplierID field) In the form obviously it displays the actual name rather than the index numerical value when the user clicks the dropdown menu. The issue (I think?) is that the VBA code I have is trying to insert a numerical value (The ID field value) rather than the text which is displayed in the dropdown menu. How can I get round this?? Apologies if this seems really simple but I have limited VBA coding experience. The PS - VBA code to insert data to the table works perfectly if the table has no lookup values in the combo boxes. These ar When I click the command button to insert the data from the form into the table
From: Al Campagna on 5 Feb 2010 11:18 Steve, Given cboSupplierID with SupplierID in the first column, and SupplierName in the second column. Setup your combobox like this... No Of Columns = 2 Column Widths = 0" ; 1.5" This allows the user to only see the SupplierName, select a SupplierName, but set the value of cboSuppplierID to the SupplierID. I think that's what you want... (usually, the cboSupplierID would be "bound" to your SupplierID table field) On my website (below) I have a sample A97 and A2003 zip file named Combo Quick Find, which demonstrates the above method. -- hth Al Campagna Microsoft Access MVP 2006-2009 http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Steve Muir" <SteveMuir(a)discussions.microsoft.com> wrote in message news:2FDDB0F7-8FA7-4594-B8AD-E157FE48A45F(a)microsoft.com... > Bit of a "noob" question but here goes.... > > I am using an unbound form for data entry purposes. I am using an unbound > form as I have some VBA code which checks that mandatory fields are filled > in > first. > > This works fine where all the fields are text boxes (i.e. not combos or > list > boxes) > but the problem I'm having is that the parent table "tblContracts" has 4 > lookup tables (which are used for combos with various fields on the form) > and > these are numeric fields which link between the tables (i.e. In the parent > table the field would be SupplierID whick links to the child table > SupplierID > field) In the form obviously it displays the actual name rather than the > index numerical value when the user clicks the dropdown menu. The issue > (I > think?) is that the VBA code I have is trying to insert a numerical value > (The ID field value) rather than the text which is displayed in the > dropdown > menu. How can I get round this?? > > Apologies if this seems really simple but I have limited VBA coding > experience. The > > PS - VBA code to insert data to the table works perfectly if the table has > no lookup values in the combo boxes. > > These ar > > When I click the command button to insert the data from the form into the > table
From: Steve Muir on 5 Feb 2010 12:34 Hi, Thanks for the quick response but it's not quite what I'm after. After reading my post I did not explain myself very well at all!! I have the combobox rowsource set up to SELECT DISTINCT Suppliername from tblSupplier (to give the user a list of current supplier names to select from) And I have the number of columns etc exactly as you suggested. The problem comes when the VBA code tries to insert the data on the form fields into the Contracts table (Parent Table). The "actual" value (as seen on the form) is the Supplier Name (not the corresponding SupplierID (PK)) But when the code tries to insert the data into the parent table (tblContracts) it is trying to insert the Text value from the textbox, rather than the SupplierID numerical value needed for the numerical field value in tblContracts (the parent table) Hope this makes sense, I might be better off attaching the form itself to illustrate what I mean. "Al Campagna" wrote: > Steve, > Given cboSupplierID with SupplierID in the first column, and > SupplierName in the second column. > Setup your combobox like this... > No Of Columns = 2 > Column Widths = 0" ; 1.5" > > This allows the user to only see the SupplierName, select a > SupplierName, but > set the value of cboSuppplierID to the SupplierID. I think that's what you > want... > (usually, the cboSupplierID would be "bound" to your SupplierID table > field) > > On my website (below) I have a sample A97 and A2003 zip file named > Combo Quick Find, which demonstrates the above method. > -- > hth > Al Campagna > Microsoft Access MVP 2006-2009 > http://home.comcast.net/~cccsolutions/index.html > > "Find a job that you love... and you'll never work a day in your life." > > "Steve Muir" <SteveMuir(a)discussions.microsoft.com> wrote in message > news:2FDDB0F7-8FA7-4594-B8AD-E157FE48A45F(a)microsoft.com... > > Bit of a "noob" question but here goes.... > > > > I am using an unbound form for data entry purposes. I am using an unbound > > form as I have some VBA code which checks that mandatory fields are filled > > in > > first. > > > > This works fine where all the fields are text boxes (i.e. not combos or > > list > > boxes) > > but the problem I'm having is that the parent table "tblContracts" has 4 > > lookup tables (which are used for combos with various fields on the form) > > and > > these are numeric fields which link between the tables (i.e. In the parent > > table the field would be SupplierID whick links to the child table > > SupplierID > > field) In the form obviously it displays the actual name rather than the > > index numerical value when the user clicks the dropdown menu. The issue > > (I > > think?) is that the VBA code I have is trying to insert a numerical value > > (The ID field value) rather than the text which is displayed in the > > dropdown > > menu. How can I get round this?? > > > > Apologies if this seems really simple but I have limited VBA coding > > experience. The > > > > PS - VBA code to insert data to the table works perfectly if the table has > > no lookup values in the combo boxes. > > > > These ar > > > > When I click the command button to insert the data from the form into the > > table > > > . >
From: Al Campagna on 5 Feb 2010 12:58 Steve, We're missing something here... I'm not sure why your controls are not bound, when you have a table to hold those values. Using VB to update your table with the unbound values after all values ahve been enetered is adding complexity for little gain. It would be better to bind the form, and check for validity at each point in the process where necessary. Zip the tables and forms needed for the problem, and email to me via my website "Contact" (below). Put "Newsgroup" in the subject, and indicate what version, and what specific tables and forms are involved... in the body. No charge... confidentiality assured. No guarantees... only a best effort basis. -- hth Al Campagna Microsoft Access MVP 2006-2009 http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Steve Muir" <SteveMuir(a)discussions.microsoft.com> wrote in message news:A3123D39-9021-456D-A457-C2E87675E965(a)microsoft.com... > Hi, > > Thanks for the quick response but it's not quite what I'm after. After > reading my post I did not explain myself very well at all!! > > I have the combobox rowsource set up to SELECT DISTINCT Suppliername from > tblSupplier (to give the user a list of current supplier names to select > from) And I have the number of columns etc exactly as you suggested. The > problem comes when the VBA code tries to insert the data on the form > fields > into the Contracts table (Parent Table). The "actual" value (as seen on > the > form) is the Supplier Name (not the corresponding SupplierID (PK)) But > when > the code tries to insert the data into the parent table (tblContracts) it > is > trying to insert the Text value from the textbox, rather than the > SupplierID > numerical value needed for the numerical field value in tblContracts (the > parent table) > > Hope this makes sense, I might be better off attaching the form itself to > illustrate what I mean. > > > "Al Campagna" wrote: > >> Steve, >> Given cboSupplierID with SupplierID in the first column, and >> SupplierName in the second column. >> Setup your combobox like this... >> No Of Columns = 2 >> Column Widths = 0" ; 1.5" >> >> This allows the user to only see the SupplierName, select a >> SupplierName, but >> set the value of cboSuppplierID to the SupplierID. I think that's what >> you >> want... >> (usually, the cboSupplierID would be "bound" to your SupplierID table >> field) >> >> On my website (below) I have a sample A97 and A2003 zip file named >> Combo Quick Find, which demonstrates the above method. >> -- >> hth >> Al Campagna >> Microsoft Access MVP 2006-2009 >> http://home.comcast.net/~cccsolutions/index.html >> >> "Find a job that you love... and you'll never work a day in your >> life." >> >> "Steve Muir" <SteveMuir(a)discussions.microsoft.com> wrote in message >> news:2FDDB0F7-8FA7-4594-B8AD-E157FE48A45F(a)microsoft.com... >> > Bit of a "noob" question but here goes.... >> > >> > I am using an unbound form for data entry purposes. I am using an >> > unbound >> > form as I have some VBA code which checks that mandatory fields are >> > filled >> > in >> > first. >> > >> > This works fine where all the fields are text boxes (i.e. not combos or >> > list >> > boxes) >> > but the problem I'm having is that the parent table "tblContracts" has >> > 4 >> > lookup tables (which are used for combos with various fields on the >> > form) >> > and >> > these are numeric fields which link between the tables (i.e. In the >> > parent >> > table the field would be SupplierID whick links to the child table >> > SupplierID >> > field) In the form obviously it displays the actual name rather than >> > the >> > index numerical value when the user clicks the dropdown menu. The >> > issue >> > (I >> > think?) is that the VBA code I have is trying to insert a numerical >> > value >> > (The ID field value) rather than the text which is displayed in the >> > dropdown >> > menu. How can I get round this?? >> > >> > Apologies if this seems really simple but I have limited VBA coding >> > experience. The >> > >> > PS - VBA code to insert data to the table works perfectly if the table >> > has >> > no lookup values in the combo boxes. >> > >> > These ar >> > >> > When I click the command button to insert the data from the form into >> > the >> > table >> >> >> . >>
From: Al Campagna on 8 Feb 2010 11:04
Stephen, You wrote in an email to me... >The form "AddNewContract" is where the code is not working. I think I know >why >**(because it is trying to insert a numeric field from the combobox, >i.e. BuyerID value, rather than BuyerFName text value) >as shown in the combobox dropdown after the user has made their >selection from the combo box. But... that's exactly what you want to do. You don't want to capture the BuyerName, or the SupplierName, you want to capture the BuyerID and the SupplierID to your table. What would you do if you had 2 buyers named John Smith? You need the "unique identifier" BuyerID and SupplierID to always be sure you have the correct associated name values. **You bound BuyerID to the combo... then why would you want the name text saved there? ------------------ You have a miscomception as to what "bound" means. Your form AddNewContract IS a bound form. You have your tblContracts as the Record Source for the form, and each of your controls is bound to a table field. Ergo... the form and the controls are "bound." "Bound" doesn't have anything to do with when, and if, the record is written to the table. --------- Since most of your NewContract fields are Required, you can not write the record to the table, unless all required fields have been valued. Control values are not written to the table until a Refresh, or Requery, or Update, occurs... or you move to another record, or the form closes. Your Save button is really a bit redundant, but that's not a big issue. You could not add another record, or close the form unless all your Required fields have been valued. ---------------- I created a query called qryYourContractDataView that shows how the IDs have been related to the appropriate name "on the fly." You would use this same process in any subsequent form, query, or report you need. I also removed the Lookup values in tblContracts, so you can actually see the ID values. I've always avoided table Lookup definitions, as they can become very confusing to bug-shoot. That's not to say they can't be used. I just prefer to do it the "meat and potatoes" way. Also, I removed the DataEntry = Yes from form AddNewContract (should be named frmAddNewContract... for clarity only) so that I could see the data values of my previously entered record. ----- So... the upshot is that I didn't really "fix" anything. You had the combos set up properly, but didn't realize it... -- hth Al Campagna Microsoft Access MVP 2006-2009 http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." I'm going to add this reply to the thread on the newsgroup, so thers can see the resolution... to date. "Steve Muir" <SteveMuir(a)discussions.microsoft.com> wrote in message news:A3123D39-9021-456D-A457-C2E87675E965(a)microsoft.com... > Hi, > > Thanks for the quick response but it's not quite what I'm after. After > reading my post I did not explain myself very well at all!! > > I have the combobox rowsource set up to SELECT DISTINCT Suppliername from > tblSupplier (to give the user a list of current supplier names to select > from) And I have the number of columns etc exactly as you suggested. The > problem comes when the VBA code tries to insert the data on the form > fields > into the Contracts table (Parent Table). The "actual" value (as seen on > the > form) is the Supplier Name (not the corresponding SupplierID (PK)) But > when > the code tries to insert the data into the parent table (tblContracts) it > is > trying to insert the Text value from the textbox, rather than the > SupplierID > numerical value needed for the numerical field value in tblContracts (the > parent table) > > Hope this makes sense, I might be better off attaching the form itself to > illustrate what I mean. > > > "Al Campagna" wrote: > >> Steve, >> Given cboSupplierID with SupplierID in the first column, and >> SupplierName in the second column. >> Setup your combobox like this... >> No Of Columns = 2 >> Column Widths = 0" ; 1.5" >> >> This allows the user to only see the SupplierName, select a >> SupplierName, but >> set the value of cboSuppplierID to the SupplierID. I think that's what >> you >> want... >> (usually, the cboSupplierID would be "bound" to your SupplierID table >> field) >> >> On my website (below) I have a sample A97 and A2003 zip file named >> Combo Quick Find, which demonstrates the above method. >> -- >> hth >> Al Campagna >> Microsoft Access MVP 2006-2009 >> http://home.comcast.net/~cccsolutions/index.html >> >> "Find a job that you love... and you'll never work a day in your >> life." >> >> "Steve Muir" <SteveMuir(a)discussions.microsoft.com> wrote in message >> news:2FDDB0F7-8FA7-4594-B8AD-E157FE48A45F(a)microsoft.com... >> > Bit of a "noob" question but here goes.... >> > >> > I am using an unbound form for data entry purposes. I am using an >> > unbound >> > form as I have some VBA code which checks that mandatory fields are >> > filled >> > in >> > first. >> > >> > This works fine where all the fields are text boxes (i.e. not combos or >> > list >> > boxes) >> > but the problem I'm having is that the parent table "tblContracts" has >> > 4 >> > lookup tables (which are used for combos with various fields on the >> > form) >> > and >> > these are numeric fields which link between the tables (i.e. In the >> > parent >> > table the field would be SupplierID whick links to the child table >> > SupplierID >> > field) In the form obviously it displays the actual name rather than >> > the >> > index numerical value when the user clicks the dropdown menu. The >> > issue >> > (I >> > think?) is that the VBA code I have is trying to insert a numerical >> > value >> > (The ID field value) rather than the text which is displayed in the >> > dropdown >> > menu. How can I get round this?? >> > >> > Apologies if this seems really simple but I have limited VBA coding >> > experience. The >> > >> > PS - VBA code to insert data to the table works perfectly if the table >> > has >> > no lookup values in the combo boxes. >> > >> > These ar >> > >> > When I click the command button to insert the data from the form into >> > the >> > table >> >> >> . >> |