fill fields from table

[tjones]

I have a table (Institutions) that is filled from a link in the PS form.

On my Recomm form, see attached graphic, I have a link (institutions) that opens the table and I would like to be able to fill some of the Recomm fields based on if an entry in the table matches my data need by selecting the assigned ID of the institution.

But i still need to be able to enter data in the fields if the information is not an institution.

 

[Shallo9]

Play around with the below code...

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("tblName", dbOpenSnapshot)
rst.FindFirst ("[StudentID] = " & StudentID.Value)
If found then use the below code to populate the fields
Ex: LastName.value=rst.lastname
and so on

NB: However if the record is not found then you may need to open the database in write mode as below...
Set rst = CurrentDb.OpenRecordset("tblName")

 

[tjones]

I tried the code you left but it keeps hanging.

I do not want anything other than this information to fill

Private Sub Institutions_Click()
'To open the institutions table and select one to fill
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset(Institutions, dbOpenSnapshot)
rst.FindFirst ("[Institution] = " & Organization.Value)
'If found then us the below code to populate the fields
Institution.Value = rst.Organization
Address.Value = rst.Address
City.Value = rst.City
State.Value = rst.State
Postal Code.Value = rst.ZIP
Country.Value = rst.Country
'If not found then open the table in write mode
Set rst = CurrentDb.OpenRecordset(Institutions)
End Sub

This is setup on the "Institution" link to fill the Organization and address field if they are in the Institutions table.

 

[Beetle]

I am attempting to fill fields in the Recomm form (to Recomm table) from the Institutions table.

Your problem actually begins right there. You should not be redundantly storing this data in two different tables. All the attributes of the Institution (Institution Name, Address, City, State, etc.) can simply be retrieved from the Institutions table based on the InstitutionID. This information can then be displayed on your form (not stored in another table). This can be done several ways;

1) Via a query

2) Via a combo box using the .Column method to display values from the additional columns in the combo box.

3) Via a Domain function like DLookup (although if you have several attributes you need to retrieve I don't necessarily recommend this approach)

4) If this is Access 2010 and this is a web form you can use Macros to retrieve the appropriate values for display on your form.

In any case, you are simply displaying this information to the user, not storing it redundantly in another table.

 

[Shallo9]

'If not found then open the table in write mode
Set rst = CurrentDb.OpenRecordset(Institutions)
End Sub

Besides Beetle's suggestions, When you open a record set in write mode you got to change the RS to write mode explicitly like...
rst.AddNew
rst.Organization= Institution.Value
rst.Address= Address.Value
rst.City= City.Value
rst.State= State.Value
rst.ZIP=Postal Code.Value
rst.Country= Country.Value
rst.Update

finally
rst.Close
set rst = Nothing

HTH

 

[tjones]

Well I am advancing a little bit but still getting an error, though a different one. I have included a graphic with the code, where it is hanging and the error.

Code using is

Private Sub Institutions_Click()
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset(Institutions, dbOpenSnapshot)
rst.FindFirst ("[Institution] = " & Institution.Value)
rst.AddNew
rst!Organization = Institution.Value
rst!Address = Address.Value
rst!City = City.Value
rst!State = State.Value
rst!ZIP = ZIP.Value
rst!Country = Country.Value
rst.Update
rst.Close
Set rst = Nothing
End Sub

 

[Shallo9]

Are u sure its not
rst.FindFirst ("[Institution] = " & Organization.Value)
Also you are not checkin' if you've found the record or not...
If you've found it then you dont have to add a new record...
Anyways I can give u a hint and you work around...
If found then the recordset should be opened
Set rst = CurrentDb.OpenRecordset(Institutions, dbOpenSnapshot)
and you read it from the recorset into the fields of your form..
If you've not found then
Set rst = CurrentDb.OpenRecordset(Institutions)
rst.AddNew
and assign the values from the forms fields to the recordset.

HTH