Search for Access record, if found allow update, if not add to record. 5

[Sherman6789]

Hi,

We have a database with a table that is updated frequently by several users using various forms.

I want to add or modify a form where the user enters an ID number. If it exists in the table, it pops up and populates the fields. The user can then update any needed areas. If it DOES NOT exist, based on the fact that the ID number is not in the main table, the program will add a new record with the ID number already entered and allow the user to add the item information into the main table if the new item has an ID number (The user will not be able to change the ID number at this point since it has already been checked). If the item does not have an ID number, the item will be added to an alternate table (already created) which is keyed on the item "name" rather than the ID Number. I realize that the item can not be placed in the main table if it does not have an ID number because it will not be unique and blank ID numbers are not permitted. However the new items without ID numbers must be kept somewhere and I would not like the user to have to type it again for placing in the alternate table. That's the purpose of the alternate table. At a later date, the item will be assigned an ID number by headquaters. When the item with NO ID number is then matched, the ID number will be added to the record in the alternate table. I would like to be able to press a button and the program will evaluate the data, and any item with an ID number will be compared to the main table and if not found in the main table will be automatically added to the main table. If the newly ID item from the alternate table IS identified, certain fields in the alternate table for the item will be merged into the main table by updating those selected fields into the item of the main table. The matched items will no longer be needed in the alternate table and will be then automatically deleted.
Hope that this makes sense!

Any suggestions? Step by step instructions will be desired.

 

[BNPMike]

Access allows you to do this in a simpler way. If you create a form based on a recordset (let's say your main table for the sake of argument) then if you have a dialogue to select an id first, Access will display the form populated with the data for that id and allow update. If the id does not exist then Access will by default display the form with empty fields inviting you to add the new record. I don't think you need to do all this checking whether things are already there or not. Access does it for you - don't have a dog and bark yourself.

 

[Sherman6789]

Thanks BNPMike,

That sounds great however, I believe that Access will not let me add a record that does not have an ID number since the key field is the ID number. That would mean that the key field would be blank.

Many times packets come into the office without an ID assigned "yet". The information needs to be recorded into a table but I figured that the main table would not be the place since it had no ID number. The ID numbers are assigned by the main office in another city. That is why I concluded that the items without ID numbers must be kept in a separate table. As soon as the item receives an official ID number, the entire item needs to be placed in the main table. With that in mind, does your suggestion still apply?

Sherman6789

 

[lespaul]

Why not have an auto-incrementing PK that can be the basis of relationships in other table and just have the ID be a field that can be null and when you get the ID you can add it.

leslie

 

[Sherman6789]

To lespaul,

Thanks for your reply. I think that I understand your sugestion. Let Access assign an autonumber and not use the Official ID number be the key field. This would allow the Official ID field to be blank.

The problem that I think I see is that the Official ID number is what is used for all searches, reports, etc. It must be used to prevent diplicates being inserted by anyone. Also, the before the items are assigned an official ID, we don't need a record of them except for accounting and preparation for inserting into the main table at the right time. Adding records to the main table when some of them do not have ID numbers might cause some confusion. If a new arrival is entered into the main table and later the same item is added after an ID has been assigned, then a duplicate will have been added until the duplication is discovered and one is removed.

My original thought was to create a NEW main table which would allow all of the records to be added. Then create two query tables. One would filter out all records without official ID numbers (key field: Official ID) and the second query table would filter out all records with official ID numbers (key: Name) The first query table would then be considered the main table. As ID numbers are added, they would automatically be removed from one table and added to the other. This seems to be simular to your basic suggestion.


WRS

 

[BNPMike]

Your final paragraph describes creating two views which is the correct way to deal with this kind of situation. Apart from that your main problem seems to be the same item can be added twice:

If a new arrival is entered into the main table and later the same item is added after an ID has been assigned, then a duplicate will have been added until the duplication is discovered and one is removed

I would ask how you discover the duplication. I suspect there must be another key in the data apart from your id. Create an index on this key and Access will identify duplicates and reject them irrespective of the ids you're giving them.

 

[KimmyGoose]

I have a question. What if I wanted to do the same thing but there are two fields that I need to check for?

 

[assets]

Going back to the original problem have you look at "if nul" Then add new record.

Never give up never give in.

There are no short cuts to anything worth doing

 

[dashen]

Hi Sherman6789,

I am also having the same problems with my small access database. If you create a "Bounded" textBox to the <ID>, you will have problems of updating the current record you are on.

But if you create a Unbounded textBox, or in my case a comboBox (I use a query select to list the ID used), You can apply a filter in your form. If the <ID> is new, the filter will have no match and move your recordset to new, but there must be a way to match the unbounded comboBox value with the <ID> field. Any ideas?

********************************************
Private Sub cboAgencyNo_AfterUpdate()
Dim strSQL As String
strSQL = "[agencyNo] = " & cboAgencyNo
DoCmd.ApplyFilter wherecondition:=strSQL
End Sub
********************************************

Is there a Event handler / a funtion that recognizes that the filter has NoMatch? Because if there is an function for that, you can use a conditional statement:

'If Filter Is No Match Then
' <ID> = unbounded textbox value
'End If

 

[dashen]

Hi Sherman6789,

If you use the DLookup command in conjunction with the filter, you can bound a textBox to your <ID> with the visible set to FALSE.

When the DLookup returns a NULL value, that means the <ID> was not found, so you can copy the unbounded text to the bounded <ID> value.

Example:
**************************************************
Private Sub cboAgencyNo_AfterUpdate()
Dim strSQL As String
strSQL = "[agencyNo] = " & cboAgencyNo
DoCmd.ApplyFilter wherecondition:=strSQL
If IsNull(DLookup("[agencyNo]", "tblAgencyInfo", _
"[agencyNo]=" & cboAgencyNo)) Then
'Not found because agencyNo lookup is Null
txtAgencyNo.Value = cboAgencyNo.Value
End If
End Sub
**************************************************

Hope that helps.

 

[dashen]

To KimmyGoose,

The above code would work the same with two fields. I am assuming these two fields are your primary key?

You just need to add more criteria to the strSQL statement, and the DLookup. In the assignment part, just add two assignments instead of one.

K, that is it for me.

dashen