Lookup before append to table

[Rolls]

Am still looking for a way to do this:

Two or more fields, nulls allowed. Search for contents of one text-box per
field. If not found, append a new record to the table. Prevents duplicates
without indexing (which is not allowed if fields may be null).

Any ideas? Something like "find record ... on error ... insert into ..."

 

[Guest]

"Search for contents of one text-box per field".

What does this mean?

 

[Guest]

Sounds kind of like she's got an input form up and wants to compare data
entered into the form to data already in the table before attempting to add
new information to the table. Thus compare contents of (example) text box
[NewName] to dataTable!Name and another text box to another field in the
table and if no match on the multiple entries, then accept it as a new record.

I'd probably attack that with a NZ(DCOUNT(....)) setup using contents of the
text boxes as parameters and only add when the result is zero. What do you
think?

 

[Rolls]

Rolls (he) wants to append a record containing two or more fields -- some
but not all may be null -- onto a table without adding duplicate records.
Setting up a multi-field index would work if no fields were null/empty. But
this often is not the case. For instance:

tblPeople contains:
Prefix
FName
MName
LName
Suffix

Up to four of the five fields may be empty.

A valid name could simply be "Admin" , or Mr. Jay Ray Johnson, Jr.

So, if I have a form containing five textboxes, one per field, when I move
off the last field, what code would

a) test to see if the tblPeople already contained a name, b) do nothing
(exit) if a name is present in the table, or c) add the record if the name
is not present in the table.

Enquiring minds want to know!

 

[Guest]

Ok, we just have to check one field, not multiple fields? See if this is
enough assistance or if you need more detailed code. Assumptions for example:
This would be in the AfterUpdate event for the text box control on the form
named txt_EnteredName. The table is tbl_DataTable and it has a field in it
named fld_Name.

Prepare by declaring some variables to reference the database and table:
Dim LocalDB as Database
Dim LocalRST as Recordset

This would be on a single line beginning with If and ending with Then:


If Nz(DCount("[fld_Name]", "tbl_DataTable", "[fld_Name] LIKE " &
Me.txt_EnteredName)) = 0 Then
'add record because we found a matching name
Set LocalDB = CurrentDB
Set LocalRST = LocalDB.OpenRecordset ("tbl_DataTable")
With LocalRST
.AddNew
![fld_Name] = Me.txt_EnteredName
![anotherField] = Me.AnotherTextBox
![Field3] = Me.ThirdTextBox
.Update
.Close
End With
Set LocalRST=Nothing
LocalDB.Close
Set LocalDB = Nothing ' reclaim all resources
Else
'do nothing, no matching name found
'you could put up a "name not found" message
' or if you really don't want to do anything, then
' you don't even need this Else section.
End If

Hope this helps. P.S. not MVP Access - but I struggle along. Someone else
may offer different/better code.

If you wanted, you could even test within the .AddNew ... .Update section to
see if the various text boxes contain anything or not, and if not, don't even
mess with trying to update a field that is not a required field and does
accept Nulls. Something like:

If Me.AnotherTextBox <> "" Then
'only does this when AnotherTextBox on form is NOT empty.
!AnotherField = Me.AnotherTextBox
End If

 

[John W. Vinson]

Setting up a multi-field index would work if no fields were null/empty.

It will also work even if there are NULL fields. One of the properties of a
non-Primary Key index is "Ignore Nulls"; you *CAN* set a unique Index on a set
of fields, even if some of those fields will be empty.

John W. Vinson [MVP]

 

[Rolls]

Ok - That's good to know -- somehow I missed it.

A related question is: Suppose a subform lets a user select multiple
choices from a defined/limited list of several (the user may not add bew
choices) , but I don't want the user to
select the same choice more than once.

How do I keep duplicate records from being entered in this situation? Would
an multiple-key index do this as well?

 

[John W. Vinson]

Ok - That's good to know -- somehow I missed it.

A related question is: Suppose a subform lets a user select multiple
choices from a defined/limited list of several (the user may not add bew
choices) , but I don't want the user to
select the same choice more than once.

How do I keep duplicate records from being entered in this situation? Would
an multiple-key index do this as well?

Yes; put a unique Index on the foreign key field (the Child Link Field of the
subform) and the field into which they select from the list of choices.

John W. Vinson [MVP]

 

[Rolls]

I wasn't successful doing wgat I wanted to do until I set:

default = ""
allow zero-length string = Yes