DataBase - Unbound Data Entry Form

Asked By Steve Muir

05-Feb-10 11:01 AM

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 am 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

05-Feb-10 11:18 AM

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 is 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

05-Feb-10 12:34 PM

Hi,

Thanks for the quick response but it is not quite what I am 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.

05-Feb-10 12:58 PM

Steve,
We're missing something here...
I am 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

08-Feb-10 11:04 AM

Stephen,
You wrote in an email to me...

But... that is exactly what you want to do.  You do not 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
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."
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 is 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 have always
avoided table Lookup definitions, as they can become very confusing to
bug-shoot.  That's not to say
they cannot 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 did not really "fix" anything.  You had the combos
set up properly, but
did not realize it...
--
hth
Al Campagna
Microsoft Access MVP 2006-2009
http://home.comcast.net/~cccsolutions/index.html


I am going to add this reply to the thread on the newsgroup, so thers can see
the resolution... to date.

08-Feb-10 07:41 PM

Steve,
it is Monday evening 7:30 PM US Eastern time.  I sent your file back
to you, at 10:45 AM this morning, using the email address I received from
you.
That email also contained the text response I copied onto this thread. (see
above)
The text explanation of what I found is the most important, but I would
like
to return your file to you, or at least be able to communicate further with
you, as
necessary.

The problem...
I continue to get messages that that email still has not been delivered,
that the
system will continue to try to deliver same.
Al Campagna