Find data using command button in access 2010's form from a table in different .mdb file

[aaromic2000]

Hi I have two different access files. One is 2010 format where I have the form and another one is 2007 format where I have the table and this file is in a different path.

Is it possible to search and pull the data from table to the form using a command button. If possible please help me with some sample codes.

 

[strive4peace]

first, you will want to link to the tables ...

1. click on the EXTERNAL DATA ribbon
2. click the Access icon in the Import & Link section
3. click the "Link" option (instead of Import option) -- I do this first so I don't forget even though it is on the bottom
4. Browse to the back-end database file
5. click OK

then pick your tables when the dialog box pops up

Once you are linked, you can use the tables just like they were really in your database -- everything except change the design of them (for that, you must open the database that holds them directly)

> "pull the data from table to the form"

forms are used to add and edit data stored in tables ... they don't actually hold the data ~

> "help me with some sample codes"

you may not need any code ... click on the CREATE ribbon and then click one of the icons in the Forms group. I have a video tutorial here showing how to create a continuous form:

Learn Access -6- Create a Continuous Form in Microsoft Access
https://www.youtube.com/watch?v=norAB9QvIls&list=PL1B2705CCB40CA4CA
- includes many tips for designing forms efficiently

 

[aaromic2000]

first, you will want to link to the tables ...

1. click on the EXTERNAL DATA ribbon
2. click the Access icon in the Import & Link section
3. click the "Link" option (instead of Import option) -- I do this first so I don't forget even though it is on the bottom
4. Browse to the back-end database file
5. click OK

then pick your tables when the dialog box pops up

Once you are linked, you can use the tables just like they were really in your database -- everything except change the design of them (for that, you must open the database that holds them directly)

> "pull the data from table to the form"

forms are used to add and edit data stored in tables ... they don't actually hold the data ~

> "help me with some sample codes"

you may not need any code ... click on the CREATE ribbon and then click one of the icons in the Forms group. I have a video tutorial here showing how to create a continuous form:

Learn Access -6- Create a Continuous Form in Microsoft Access
https://www.youtube.com/watch?v=norAB9QvIls&list=PL1B2705CCB40CA4CA
- includes many tips for designing forms efficiently

Thank you very much. I'm actually not looking for holding the data in the form. May be I was not clear in my question I hope. I'm actually trying to edit data that is already added using the form. For editing I have to retrieve the fields from the row to the form again. That is what I'm actually trying to do.

 

[strive4peace]

you're welcome

Make one or more unbound (no ControlSource) combos on your form (like in the header). Let the first column be invisible and be the primary key ID of the RecordSource of your form and then, on its AfterUpdate event...

=FindRecord()

this code goes behind the form:

'~~~~~~~~~~~~~~~~~~~~
Private Function FindRecord()
  
   'if nothing is picked in the active control, exit
   If IsNull(Me.ActiveControl) Then Exit Function
  
   'save current record if changes were made
   If me.dirty then me.dirty = false
  
   'declare a variable to hold the primary key value to look up
   Dim nRecordID As Long
 
   'set value to look up by what is selected
   nRecordID= Me.ActiveControl
 
   'clear the choice to find
   Me.ActiveControl = Null
 
   'find the first value that matches
   with Me.RecordsetClone
       .FindFirst "SomeID = " & nRecordID
 
      'if a matching record was found, then move to it
      If Not .NoMatch Then
         Me.Bookmark = .Bookmark
      End If
   end with

End Function
 
'~~~~~~~~~~~~~~~~~~~~

where
- SomeID is the Name of the primary key field, which is in the RecordSource of the form -- assuming your primary key is a Long Integer data type (autonumbers are long integers)

Remember that the Rowsource for a combo can come from anywhere -- it can pull from multiple tables or only use one ... just make sure that the first column is the primary key ID of the table you want to search (and that field is part of the RecordSource for the form you are searching).

If you are searching the recordset on another form, change the FindRecord name to be specific (like FindRecord_Order) and, substitute

Me --> forms!formname

If on a subform:
Me --> Me.subform_controlname.form

~ HaPpY NeW yEaR ~