searching existing value while populating a form

[hksahoo]

I have a table where Sender's name and addresses are stored. While adding a new record through a form,
I want to search for the existing sender based on the sender's name and PIN or Sender ID from the Sender Table and if (found), then the form should automatically populate the values in the corresponding fields in a new record. How do I do it?

hksahoo

[June7]

You probably should not be saving the related info from Sender table into the new data entry record - that is duplication of data. Just save the record ID from Sender table.

If you use a combobox to select Sender then there is no need to do a 'search'.

[hksahoo]

But all the sender's do not have sender ID. Because there may be new senders whose data is not available in the sender's table.

Ya, I use a combo box to select a sender. The purpose of search is to avoid entering the Sender's detail address in the new record. In the new record I like to add other details of the sender other than his address.

So how do I add a new address while selecting the address through a combo box?

[June7]

Options:

1. manually open the Sender table and add record then manually refresh the form so the new record is now available in the combobox

2. use VBA code behind the combobox NotInList event to add record record to Sender table and refresh the combobox, this was suggested in your other thread https://www.accessforums.net/forms/a...ile-38822.html

[hksahoo]

I studied the NotInList event Tutorial and tried to write the code. But I am getting error " Method or Data" Not found. The screenshot and the db is attached is attached. Pl help.

Regds.
hksahoo

[hksahoo]

I added the following code in the NotInTheList event of Sender_Name but getting the Error "424" Object Required in the line "Form_SENDER.Name = NewData". Pl. help to resolve the problem.
I attached the db earlier. But now I have added one form Sender for Sender Table.

Private Sub Sender_Name_NotInList(NewData As String, Response As Integer)
Response = acDataErrContinue
Call Sender_Name_Not_Found(NewData)
End Sub


Public Sub Sender_Name_Not_Found(NewData)
Dim ans As Variant
' new Name
gbl_exit_name = False


ans = MsgBox("Do you want to add this Name?", _
vbYesNo, "Add New Name?")


If ans = vbNo Then
Me.SENDER_NAME = Null
DoCmd.GoToControl "Sender_Name"
GoTo exit_it
End If


' add Name
DoCmd.OpenForm ("SENDER")
Form_SENDER.Name = NewData


Me.Name = Null


DoCmd.GoToControl "House_Number"


exit_it:


End Sub

[June7]

I tried downloading the file but get error that the zip is corrupt or invalid.

[hksahoo]

I tried downloading the file but get error that the zip is corrupt or invalid.

Attaching the file again. Pl. help.

[June7]

Why are there duplicate names in SENDER table? Are KABITA DALAL and KABITA DEVI really so common? Name parts should be separate fields. The SENDER_NAME field in Registration should be number type because the ID field in SENDER is autonumber. Primary and foreign key fields must be same type to allow joins in queries. You have Lookup set in table, suggest you not do that http://access.mvps.org/access/lookupfields.htm.

Is address info in Registration the address of sender? That info should be in SENDER table. Why is PIN saved in Registration? This is duplicating data.

'Name' is a reserved word. Should not use reserved words as names for anything, especially for fields. This is causing confusion in Access when you reference field named Name because Name is a property. Reserved words used as names must be enclosed in [], however, event that doesn't fix this issue. Rename the field in SENDER table. When I did the name change, Access would not recognize the field/textbox on SENDER form. I deleted then recreated the textboxes and then Access recognized the field.

Need to open the SENDER form to a new record. DoCmd.OpenForm ("SENDER"), , , , acFormAdd

Need code behind the SENDER form to save record and requery the combobox on Registration form.

Also should avoid spaces in names.

Every module should have these 2 lines in header:
Option Compare Database
Option Explicit

The gbl_exit_name = False line is not explained by the tutorial and doesn't seem relevant so comment it out or delete.

Code behind Registration:

Option Compare Database
Option Explicit

Private Sub Sender_Name_NotInList(NewData As String, Response As Integer)
Response = acDataErrContinue
Call Sender_Name_Not_Found(NewData)
End Sub

Public Sub Sender_Name_Not_Found(NewData)
Dim ans As Variant
' new Name
'gbl_exit_name = False
ans = MsgBox("Do you want to add this Name?", _
vbYesNo, "Add New Name?")
If ans = vbNo Then
    Me.SENDER_NAME = Null
    DoCmd.GoToControl "Sender_Name"
    GoTo exit_it
End If
' add Name
DoCmd.OpenForm ("SENDER"), , , , acFormAdd
Form_SENDER.SENDER_NAME = NewData
DoCmd.GoToControl "PIN"
Me.SENDER_NAME = Null
Me.HOUSE_NUMBER.SetFocus
exit_it:
End Sub

Code behind command button on Sender:

Option Compare Database
Option Explicit

Private Sub btnClose_Click()
If CurrentProject.AllForms("Registered Letters").IsLoaded Then
    [Form_Registered Letters].[SENDER_NAME].Requery
End If
DoCmd.Close
End Sub

Might want to set the Sender form Cycle property to CurrentRecord.