Populate table with results from search

[shelter]

I run Access 2003. Here's what I'm trying to do: Users click a
search button which opens frmSearch. Users enter the first and/or last
name of a person and press enter, which displays a list of possible
records. Users can doubleclick any record in the list, which displays
the record in frmPerson or click New which opens frmPerson so they can
enter a new record. All this works fine.

Where I get messed up is that upon closing the record, I want to give
users an option to "use" the record or not. If yes,
tblPerson.PersonID populates tblRelationship.AssociateID. If not, the
record is simply closed.

I am not well-versed in writing code, so could really use some help.
Thank you.

 

[banem2]

I run Access 2003.  Here's what I'm trying to do:  Users click a
search button which opens frmSearch. Users enter the first and/or last
name of a person and press enter, which displays a list of possible
records.  Users can doubleclick any record in the list, which displays
the record in frmPerson or click New which opens frmPerson so they can
enter a new record. All this works fine.

Where I get messed up is that upon closing the record, I want to give
users an option to "use" the record or not.  If yes,
tblPerson.PersonID populates tblRelationship.AssociateID.  If not, the
record is simply closed.

I am not well-versed in writing code, so could really use some help.
Thank you.

If MsgBox("Use Record?", vbQuestion+vbYesNo, "Use Record") = vbYes
Then
'run your code which populate tblRelationship
End If

You can populate record on many ways, maybe with SQL command as best
solution. Create query which do that and simply call query, or copy
SQL from this query in above code and execute it with:
CurrentDb.Execute "your SQL".

Regards,
Branislav Mihaljev, Microsoft Access MVP

 

[shelter]

If MsgBox("Use Record?", vbQuestion+vbYesNo, "Use Record") = vbYes
Then
'run your code which populate tblRelationship
End If

You can populate record on many ways, maybe with SQL command as best
solution. Create query which do that and simply call query, or copy
SQL from this query in above code and execute it with:
CurrentDb.Execute "your SQL".

Regards,
Branislav Mihaljev, Microsoft Access MVP

Thank you. I will give this a try and post back.

 

[shelter]

Thank you.  I will give this a try and post back.- Hide quoted text -

- Show quoted text -

I would like to explain further what I'm trying to do...

I have a frmPerson, with multiple tabs. One of the tabs contains
subfrmRelationship. Its purpose is to allow the user to link another
person's record to the current record (ie; the current record is Mom
Smith, and the user wants to show Child1 Smith and Child2 Smith as her
children. The resulting table should have three fields--the PersonID
of the main record, the PersonID of the related record and the
relationship type.

So, from subfrmRelationships, the user searches for or adds a Person
record (via frmPerson), indicates whether to use that record, if yes,
return to the initial subfrmRelationship and populate tblRelationships
with PersonID of the searched for/added record. If no, return to the
initial Person record with no further action. Is this the best way to
do this, and if so, how do I accomplish it?

Thank you for your time.

 

[banem2]

I would like to explain further what I'm trying to do...

I have a frmPerson, with multiple tabs. One of the tabs contains
subfrmRelationship. Its purpose is to allow the user to link another
person's record to the current record (ie; the current record is Mom
Smith, and the user wants to show Child1 Smith and Child2 Smith as her
children. The resulting table should have three fields--the PersonID
of the main record, the PersonID of the related record and the
relationship type.

So, from subfrmRelationships, the user searches for or adds a Person
record (via frmPerson), indicates whether to use that record, if yes,
return to the initial subfrmRelationship and populate tblRelationships
with PersonID of the searched for/added record. If no, return to the
initial Person record with no further action. Is this the best way to
do this, and if so, how do I accomplish it?

Thank you for your time.

If you are able to create query which will populate table with records
the way you want it, then use code from my first post and call query
from code. Let me know if you have trouble with query.

Regards,
Branislav Mihaljev, Microsoft Access MVP

 

[shelter]

If you are able to create query which will populate table with records
the way you want it, then use code from my first post and call query
from code. Let me know if you have trouble with query.

Regards,
Branislav Mihaljev, Microsoft Access MVP- Hide quoted text -

- Show quoted text -

Thank you for responding. I guess I'm having a hard time with the
concept of a query populating this table, since a search is necessary
beforehand to make sure the correct record ID is populating the
resulting table. Further help with the query would be appreciated if
you have time, thank you.

DJohnson

 

[banem2]

Thank you for responding. I guess I'm having a hard time with the
concept of a query populating this table, since a search is necessary
beforehand to make sure the correct record ID is populating the
resulting table. Further help with the query would be appreciated if
you have time, thank you.

DJohnson

Well, I will need to see the database (relationships, tables, field
names) then precisely what you want to do in order to write a query. I
assume you need both tables in query (Append Query) to avoid
duplicating records. Looks like overall simple query, so try to create
one. When you have it working you can use this code (as told):

Dim strSQL As String
If MsgBox("Use Record?", vbQuestion+vbYesNo, "Use Record") = vbYes
Then
strSQL = "Copy here SQL from query"
CurrentDB.Execute strSQL
End If

Program ask user if he/she wants to use selected record and, if "yes",
it will copy record(s) to destination table. Hope this helps.

Regards,
Branislav Mihaljev, Microsoft Access MVP