Change record source of a field

[Guest]

I have set up a query to auto fill two fields. The query is working
correctly but I need to know how to change the record source of the two
fields that are to be auto-filled to the query instead of the table all the
other fields are based on. Any ideas?

 

[Guest]

Unless I am misreading your post, it sounds like you want all but two bound
controls on a form based on a table and these two bound to your query.
You can't do that. A form can have only one record source and the form's
controls can be bound only to fields in the recordset.

Now, that doesn't mean you can't accomplish your actual goal. Post back
with what you need to do to populate these two controls and perhaps we can
suggest a method that works.

Also, please try to use correct terminology so your question is clear.
Fields are not on forms. Fields are in tables, Forms have controls.

 

[Guest]

Sorry about that. I need to have these two controls to auto-fill when a
certain value is placed in another control on the form. When I looked in
help on Access it said to create a query(whichI did) but it did not say how
to connect that query to the form so I'm stuck

 

[Guest]

Whether or not you really need a query depends on what you want to put into
the controls. Regardless of the method you use to load data into the
controls, your code should be in the After Update of the even where the user
enters the value used to determine what to assign to the other controls. To
use a query requires establishing a recordset and reading the data from the
query fields and assigning the field values to the other controls. For
example:

Dim qdfLookUp As QueryDef
Dim rstLookUp as Recordset

Set qdfLookUp = dbs.QueryDefs("MYQueryName")
qdfLookup.Parameters(0) = Me.FirstControl
Set rstLookup = qdfLookup.OpenRecordset(dbOpenSnapshot, dbReadOnly)

If rstLookup.RecordCount = 0 Then
MsgBox "No Matching Data"
Else
Me.SecondControl = rstLookup![Field1]
Me.LastControl = rstLookup![Field2]
End If

set rstLookup = Nothing
set qdfLookup = Nothing

Sometimes, it is easier to use the DLookup function to accomplisth the same
thing:

Me.SecondControl = DLookup("[Field1]", "TableNameHere", "[FieldToMatch] =
'" & Me.FirstControl & "'")

 

[Guest]

Thank you! I'll try this

Whether or not you really need a query depends on what you want to put into
the controls. Regardless of the method you use to load data into the
controls, your code should be in the After Update of the even where the user
enters the value used to determine what to assign to the other controls. To
use a query requires establishing a recordset and reading the data from the
query fields and assigning the field values to the other controls. For
example:

Dim qdfLookUp As QueryDef
Dim rstLookUp as Recordset

Set qdfLookUp = dbs.QueryDefs("MYQueryName")
qdfLookup.Parameters(0) = Me.FirstControl
Set rstLookup = qdfLookup.OpenRecordset(dbOpenSnapshot, dbReadOnly)

If rstLookup.RecordCount = 0 Then
MsgBox "No Matching Data"
Else
Me.SecondControl = rstLookup![Field1]
Me.LastControl = rstLookup![Field2]
End If

set rstLookup = Nothing
set qdfLookup = Nothing

Sometimes, it is easier to use the DLookup function to accomplisth the same
thing:

Me.SecondControl = DLookup("[Field1]", "TableNameHere", "[FieldToMatch] =
'" & Me.FirstControl & "'")

Sorry about that. I need to have these two controls to auto-fill when a
certain value is placed in another control on the form. When I looked in
help on Access it said to create a query(whichI did) but it did not say how
to connect that query to the form so I'm stuck

 

[Guest]

Candace,

Simply change the form's RecordSource property to the name of the query, and
set the ControlSource property of each of your controls to the name of the
field from the query you'd like to display.

As in all cases where you base a form on a query that joins two tables,
include the foreign key field but not the associated primary key from the
linked table. For example, if you link a Customers table to the Orders table
for an Order entry form, link the two on CustID, and include the CustID field
from the Orders table, but not from the Customer table, otherwise the query
will be "non-updateable".

Sprinks