Need help with WHERE

[Guest]

Hello everyone! Using A02 on XP. Not a programmer but learning bit by bit.

I'm using the code below to allow users to fill in a few fields with the
agent data if it applies, otherwise they key in the data. However, I need the
agent info that applies to the contract record that is showing on my form.
The text field is: Forms![fAcceptanceLetter]![PlanNum].

How would I insert a WHERE clause below? Can I? Am I on the right track here?

Private Sub AgtInfo_Click()
'Open a recordset on qInsertAgentInfo
Dim rstR As DAO.Recordset
Set rstR = CurrentDb.OpenRecordset("qInsertAgentInfo")

'Update the values in the subform
Forms![fAcceptanceLetter]![SOURCECOMPANY] = _
rstR.Fields("AgencyName").Value
Forms![fAcceptanceLetter]![SOURCECONTACT] = _
rstR.Fields("Agent Name").Value
Forms![fAcceptanceLetter]! = _
rstR.Fields("AgtEmail").Value
Forms![fAcceptanceLetter]![PHONENUM] = _
rstR.Fields("AgtPhone#").Value
Forms![fAcceptanceLetter]![FAXNUM] = _
rstR.Fields("AgtFax#").Value
rstR.close

End Sub

Thanks in advance for any help or advice!!!

 

[Alex White MCDBA MCSE]

If I am reading you right,

create a strSQL variable

e.g.

dim strSQL as string

get the SQL Text from the query qInsertAgentInfo

because I don't know what the contents are I am going to Guess

Select * from Agent

All you need to do is

strSQL = "Select * from Agent Where Agent_ID=1" ' check and change

Set rstR = CurrentDb.OpenRecordset(strSQL)

but can I say it looks to me like you are duplicating information from one
table to another, if I am correct you should not do this, good database
design is every piece of information should only be in the database once,
rarely duplicated, but I could be wrong based on I cannot see the content of
the query,

post the SQL text of the query and I will have a look for you.

 

[Guest]

Hi Alex! Thanks very much for getting on this so quickly.

I have a table that is recording processing work on existing contracts.
Linked by PlanNum, it keeps track of requests and inquiries from various
sources. Sometimes this contact is with the agent. Rather than key in the
agent name and phone/fax/email info, I'd like to have a button to click on
and insert the agent data. The reason to insert the data into this table is
that during the lifetime of a contract, agents can change. I may contact
Agent Smith in 1998 and in 2000 contact Agent Jones.

My form's table, tAcceptanceLetter, has the contract number so I included
the contract number in my query, qInsertAgentInfo. Here is the SQL:

SELECT [InformationTable].[PlanNum], [AgentInfoTable].[AgcyAgtNum],
[AgentInfoTable].[Agent Name], [AgentInfoTable].AgencyName,
[AgentInfoTable].[AgtPhoneNum], [AgentInfoTable].[AgtFaxNum],
[AgentInfoTable].[AgtEmail]
FROM [AgentInfoTable] RIGHT JOIN [InformationTable] ON
[AgentInfoTable].[AgcyAgtNum] = [InformationTable].[AgcyAgtNum]
WITH OWNERACCESS OPTION;

So, when I click on the button, I would like to open a recordset (below) of
the agent fields for that one specific contract number (just one record) and
update the fields on my form. (Don't I have to be sure to use ALL the fields
in the underlying query? So, technically, am I also updating the contract
number field?) Would I be better off just creating a small, invisible subform
linked by contract number and update the fields from there with the click?
That's how I would normally do it but trying to learn better ways.

Private Sub AgtInfo_Click()
'Open a recordset on qInsertAgentInfo
Dim rstR As DAO.Recordset
Set rstR = CurrentDb.OpenRecordset("qInsertAgentInfo")
'Update the values in the subform
Forms![fAcceptanceLetter]![SOURCECOMPANY] = _
rstR.Fields("AgencyName").Value
Forms![fAcceptanceLetter]![SOURCECONTACT] = _
rstR.Fields("AgentName").Value
Forms![fAcceptanceLetter]! = _
rstR.Fields("AgtEmail").Value
Forms![fAcceptanceLetter]![PHONENUM] = _
rstR.Fields("AgtPhoneNum").Value
Forms![fAcceptanceLetter]![FAXNUM] = _
rstR.Fields("AgtFaxNum").Value
rstR.close
End Sub

I hope I'm describing this sufficiently. I am very grateful for your help on
this!

 

[Alex White MCDBA MCSE]

Hi Bonnie,

I still thing you would be better off doing the following,

Only link to the AgentInfoTable, the best way is get the primary key for the
AgentInfoTable say it's Agent_ID, create that field in the InfomationTable,
link only the Agent_ID, create a sub form on your main form this sub form is
agent details, don't allow edit/delete/add, and link via the Agent_ID field
on both, this means by changing the Agent_ID field on the InformationTable,
you have updated the underlying Agent Details on the subform.

From the perspective of updates, I will give you one example,

An Agent changes their telephone number, how do you update all the relevant
records? Well with my way change the telephone number in the AgentInfoTable
job done.

Personally I think you have over complicated something that is quite simple,
simple is good.

Need more info post back

--
Regards

Alex White MCDBA MCSE
http://www.intralan.co.uk

Hi Alex! Thanks very much for getting on this so quickly.

I have a table that is recording processing work on existing contracts.
Linked by PlanNum, it keeps track of requests and inquiries from various
sources. Sometimes this contact is with the agent. Rather than key in the
agent name and phone/fax/email info, I'd like to have a button to click on
and insert the agent data. The reason to insert the data into this table
is
that during the lifetime of a contract, agents can change. I may contact
Agent Smith in 1998 and in 2000 contact Agent Jones.

My form's table, tAcceptanceLetter, has the contract number so I included
the contract number in my query, qInsertAgentInfo. Here is the SQL:

SELECT [InformationTable].[PlanNum], [AgentInfoTable].[AgcyAgtNum],
[AgentInfoTable].[Agent Name], [AgentInfoTable].AgencyName,
[AgentInfoTable].[AgtPhoneNum], [AgentInfoTable].[AgtFaxNum],
[AgentInfoTable].[AgtEmail]
FROM [AgentInfoTable] RIGHT JOIN [InformationTable] ON
[AgentInfoTable].[AgcyAgtNum] = [InformationTable].[AgcyAgtNum]
WITH OWNERACCESS OPTION;

So, when I click on the button, I would like to open a recordset (below)
of
the agent fields for that one specific contract number (just one record)
and
update the fields on my form. (Don't I have to be sure to use ALL the
fields
in the underlying query? So, technically, am I also updating the contract
number field?) Would I be better off just creating a small, invisible
subform
linked by contract number and update the fields from there with the click?
That's how I would normally do it but trying to learn better ways.

Private Sub AgtInfo_Click()
'Open a recordset on qInsertAgentInfo
Dim rstR As DAO.Recordset
Set rstR = CurrentDb.OpenRecordset("qInsertAgentInfo")
'Update the values in the subform
Forms![fAcceptanceLetter]![SOURCECOMPANY] = _
rstR.Fields("AgencyName").Value
Forms![fAcceptanceLetter]![SOURCECONTACT] = _
rstR.Fields("AgentName").Value
Forms![fAcceptanceLetter]! = _
rstR.Fields("AgtEmail").Value
Forms![fAcceptanceLetter]![PHONENUM] = _
rstR.Fields("AgtPhoneNum").Value
Forms![fAcceptanceLetter]![FAXNUM] = _
rstR.Fields("AgtFaxNum").Value
rstR.close
End Sub

I hope I'm describing this sufficiently. I am very grateful for your help
on
this![/QUOTE]