Searching/adding new records

[Guest]

I would like to change my main forms. I have several different ones and each
main form shows the company with the products they supply and their main
customers in two different subforms. The different forms separate the
companies by which type of products they supply, so companies may appear on
more than one form. I would like it so that when i need to enter a new
record on a main form, when i insert the company name a lookup is done to
find the company_ID and all of its important information that is stored in
the companies table. If the company name is not found i would like a new
unique company_id to be associated with that company. is this at all
possible? thank you for any help you can provide.

 

[Jeff Boyce]

How does Access know which form to use to show which company?

If you are only using different forms to show different companies, and the
information about each of the different companies is coming all from the
same source/table, you don't need separate forms.

Another approach would be to have a form that displays company information.
In the header of that form, you could have an unbound combo box that lets
you lookup a company. If you select a company from the list, use the combo
box's AfterUpdate event to requery the form (and base the source data for
the form on a query that uses the combo box as a criterion).

If you type in a company that doesn't exist in your "company" table, you can
use the NotInList event (set LimitToList property to Yes) to pop open a form
to gather the new company's info, then return you to the original combo box,
with the new company added.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Hi

1st you need to check if the company exists on the database and then – if
not – have the option of adding a new record or searching again

So

Create a combo ([ComboSearch] or some other name)
And on the AfterUpdate actin use something like this

Private Sub ComboSearch_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[FieldID] = " & Str(Me![ComboSearch])
Me.Bookmark = rs.Bookmark
Me.SomeFieldName.SetFocus
End If
Me.ComboSurnameSearch = ""
End Sub

This section Me.SomeFieldName.SetFocus
Will set the focus to the company name or some other field (delete it if you
don’t want this or simply alter the field name)

This section rs.FindFirst "[FieldID] = " & Str(Me![ComboSearch])
Needs to have the correct ID or primary field of the recordset

This next action is called if the text string you put into the [SearchCombo]
is not found


On NotOnList action place this

Private Sub ComboSearch_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim Msg As String

Msg = "'" & NewData & "' is not on the database." & vbCr & vbCr
Msg = Msg & "Add New Record ? Or some other message"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
MsgBox "Have another try.Or some other message"
Else
Set Db = CurrentDb
Set rs = Db.OpenRecordset("tblClients", dbOpenDynaset)

rs.AddNew
rs![CDSurname] = NewData
rs.Update
Response = acDataErrAdded
End If
End Sub

Let me know if this is helpful (took a while to do so may have made a "few"
< slipups

 

[Guest]

Sorry it took so long to respond to your message, I finaly got around to your
suggestion. Thank you very much for trying to help. Here are the problems
that i have right now. If the AfterUpdate part of your code, there is an
"End If" but no beginning "If". Do you know where it needs to be placed?
Also, the combo box should be unbound? In the NotInList section of the code
you have :
Set rs = Db.OpenRecordset("tblClients", dbOpenDynaset)
Where do you come up with "tblClients"? Should this refer to one of the
tables in my DB and if so which one? Thank you very much.

Hi

1st you need to check if the company exists on the database and then – if
not – have the option of adding a new record or searching again

So

Create a combo ([ComboSearch] or some other name)
And on the AfterUpdate actin use something like this

Private Sub ComboSearch_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[FieldID] = " & Str(Me![ComboSearch])
Me.Bookmark = rs.Bookmark
Me.SomeFieldName.SetFocus
End If
Me.ComboSurnameSearch = ""
End Sub

This section Me.SomeFieldName.SetFocus
Will set the focus to the company name or some other field (delete it if you
don’t want this or simply alter the field name)

This section rs.FindFirst "[FieldID] = " & Str(Me![ComboSearch])
Needs to have the correct ID or primary field of the recordset

This next action is called if the text string you put into the [SearchCombo]
is not found


On NotOnList action place this

Private Sub ComboSearch_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim Msg As String

Msg = "'" & NewData & "' is not on the database." & vbCr & vbCr
Msg = Msg & "Add New Record ? Or some other message"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
MsgBox "Have another try.Or some other message"
Else
Set Db = CurrentDb
Set rs = Db.OpenRecordset("tblClients", dbOpenDynaset)

rs.AddNew
rs![CDSurname] = NewData
rs.Update
Response = acDataErrAdded
End If
End Sub

Let me know if this is helpful (took a while to do so may have made a "few"
< slipups

--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.

I would like to change my main forms. I have several different ones and each
main form shows the company with the products they supply and their main
customers in two different subforms. The different forms separate the
companies by which type of products they supply, so companies may appear on
more than one form. I would like it so that when i need to enter a new
record on a main form, when i insert the company name a lookup is done to
find the company_ID and all of its important information that is stored in
the companies table. If the company name is not found i would like a new
unique company_id to be associated with that company. is this at all
possible? thank you for any help you can provide.