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

[schnuked852]

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]

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]

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]

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]

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]

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]

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]

I would suggest that you are making it over complicated.

 

[Gasman]

See the DB that arnelgp uploaded for this thread.
It does pretty much what you are asking, only adds 16 records instead.

 

[mike60smart]

You would at some stage need to add other details about the Company so why not do it all in one go?

 

[schnuked852]

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.