Searching for record and option to add new record if not found (1 Viewer)

schnuked852

New member
Local time
Today, 10:25
Joined
Aug 5, 2021
Messages
5
Hi All,

I was wondering if there is a way in single form to search for an existing record and if that record is not found present the option to add a new record. I have the "search" part sorted using a text box and list box. But not sure how to "present the option to add new record if record is not found"

I am trying to crate a simple CRM database, but want the end users to have the ability to search for a company, if that company is not found have the user create a new company. For context, the company and individuals are stored in different tables.

Any advice would be greatly appreciated!

Thank you
 

CJ_London

Super Moderator
Staff member
Local time
Today, 10:25
Joined
Feb 19, 2013
Messages
14,693
Depends how your form is built but perhaps something like this pseudo code in your search after update event

if no records returned from search then
Me.dataentry =msgbox(“no record found, do you want to add a new one?,vbyesno)=vbyes
 

schnuked852

New member
Local time
Today, 10:25
Joined
Aug 5, 2021
Messages
5
Thanks CJ_London! What did you mean by "depends on how your form is built"? Were there some details I could provide that would help?

But I think I'll try the msgbox and see how it goes.
 

mike60smart

Registered User.
Local time
Today, 10:25
Joined
Aug 6, 2017
Messages
1,235
Hi
Are you saying that a Company has a number of Individuals ?

If this is your structure then the tblCompany would have a Primary Key - Autonumber named CompanyID

Then you should have a related table named tblCompanyMembers

This table would have CompanyMemberID as the Primary Key
With a Foreign Key - Long Integer - field named CompanyID

You would then link these 2 tables using the CompanyID field and Enforce Referential Integrity

You can then create a Main form based on the table tblCompany
With a Subform based on tblCompanyMembers

This Main Form would then allow you to select a Company and be able to add a number of Members associated with the Company.
 

schnuked852

New member
Local time
Today, 10:25
Joined
Aug 5, 2021
Messages
5
Thanks, mikes60smart. That's exactly how my tables are currently structured. The problem I was dealing with was not how to link individuals to a company. But rather how to make a form to i) search for a company in tblCompany and then ii) have the option to add a new company to the tblCompany if the search has no results.
 

mike60smart

Registered User.
Local time
Today, 10:25
Joined
Aug 6, 2017
Messages
1,235
Thanks, mikes60smart. That's exactly how my tables are currently structured. The problem I was dealing with was not how to link individuals to a company. But rather how to make a form to i) search for a company in tblCompany and then ii) have the option to add a new company to the tblCompany if the search has no results.
So have you created the Form to deal with data entry to the 2 tables ?

If you have then all you need is a Combobox in the Header of the Main form to allow you to search for a Company.
If the Company is not in the list displayed you then would have a Command Button in the Header to Add a New record.
 

schnuked852

New member
Local time
Today, 10:25
Joined
Aug 5, 2021
Messages
5
Right your suggestion makes a lot of sense and I may go that direction.

What I currently have setup is a form that is connected to the tblCompany. I have a listbox that is tied to a textbox and in VBA I have this query.

Me.ListBox.RowSource = "SELECT Company.CompanyName FROM [Company] WHERE (Company.CompanyName) Like '*" & Me.textbox& "*' "

This works well to search and display the company names in tblCompany. What I was hoping to achieve is to populate the list box with a new value if the query returns no results. The new value (call it "<ENTER NEW COMPANY>") if double-clicked would add the whatever is the in the textbox into the tblCompany.

Is this overly ambitious?
 

mike60smart

Registered User.
Local time
Today, 10:25
Joined
Aug 6, 2017
Messages
1,235
I would suggest that you are making it over complicated.
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:25
Joined
Sep 21, 2011
Messages
10,849
See the DB that arnelgp uploaded for this thread.
It does pretty much what you are asking, only adds 16 records instead.
 

mike60smart

Registered User.
Local time
Today, 10:25
Joined
Aug 6, 2017
Messages
1,235
You would at some stage need to add other details about the Company so why not do it all in one go?
 

schnuked852

New member
Local time
Today, 10:25
Joined
Aug 5, 2021
Messages
5
Thanks, Gasman. I'll have a read!

mike60smart - at the moment I am just more curious now if this would work. But you are right, at some stage I will need to add more details about the company so it might makes sense to launch a new form instead of inserting into the table what is in the text field.
 

Users who are viewing this thread

Top Bottom