Using ADO to fill in form from another DB

[RON]

I have a database, lets call it DB1.mdb I am building a form on this
database to display student information The form has NO Recordsource and
all textboxes on the form like txtStudentFirstName, txtStudentLastName,
txtAddress, are UNBound. I have buttons First Record, Next Record and when
those are pressed I want to move through my STUDENTS TABLE and put that info
into the textboxes.

On that same form I have UNBOUND text boxes that display
txtEmergencyContctLastName, txtEmergencyContactFirstName these text boxes
will also get filled in when I move through records. However, those text
boxes will get filled in from a database called CONTACTS.mdb and a table in
that database called CONTACT

Using ADO and connecting to this CONTACTS.mdb daabase, how do I do this?

thanks for any and all help.

 

[Guest]

Just curious, but why do it the hard way? Make the form bound to a query
that returns the fields you need. If you're concerned about someone changing
something, just lock those fields or make the form read-only.

If you need to display multiple emergency contacts for each student, you can
use a linked subform.

Bruce

 

[RON]

I need to do it this way.

Just curious, but why do it the hard way? Make the form bound to a query
that returns the fields you need. If you're concerned about someone
changing
something, just lock those fields or make the form read-only.

If you need to display multiple emergency contacts for each student, you
can
use a linked subform.

Bruce

 

[David W. Fenton]

I need to do it this way.

Why? Nobody is going to take the time to laboriously explain it to
you until you've shown that it's not counterproductive to do it with
ADO.

 

[Guest]

Sorry, David, but I was bored....
Bruce

Here's your homework, Ron:

Option Compare Database
Option Explicit

'Note: This was written for DAO, not ADO

Dim rstS As Recordset
Dim rstC As Recordset

Private Sub Form_Close()
'Clean up
rstS.Close
Set rstS = Nothing
Set rstC = Nothing
End Sub

Private Sub Form_Load()
'Open table
Set rstS = CurrentDb.OpenRecordset("tblStudents")
'Test for records.
If rstS.RecordCount = 0 Then
MsgBox "No data."
DoCmd.Close
Exit Sub
End If
'Load txt boxes with values from this record.
LoadFields
End Sub

Private Sub LoadFields()
On Error GoTo Oops

Dim fld As Field
Dim myStr As String
Dim mySQL As String

'Load student textboxes.
'If you construct your textbox names to match your source table field
' names, you can use a loop to load them for each new record.

For Each fld In rstS.Fields
myStr = "txt" & fld.Name
Me(myStr).Value = Nz(fld.Value, "")
Next fld

mySQL = "SELECT ContactName, ContactPhone " & _
"FROM tblContacts " & _
"WHERE StudentKey = " & rstS![StudentKey] & ";"
Set rstC = CurrentDb.OpenRecordset(mySQL)

'This only loads the first contact entry.
' (If more than one per student a subform is reqd.)
If rstC.RecordCount > 0 Then
Me![txtContactName] = Nz(rstC![ContactName], "")
Me![txtContactPhone] = Nz(rstC![ContactPhone], "")
End If
rstC.Close

Exit Sub

Oops:
Resume Next
End Sub

Private Sub NextStudent_Click()
'Add logic to prevent reading past end.
rstS.MoveNext
LoadFields
End Sub

Private Sub PrevStudent_Click()
'Add logic to prevent reading above first record.
rstS.MovePrevious
LoadFields
End Sub

 

[Guest]

Hi Bruce,

To prevent a possible run-time error with your solution, I recommend
explicitly declaring the recordset variables, ie:

Dim rstS As Recordset
Dim rstC As Recordset

Should be:

Dim rstS As DAO.Recordset
Dim rstC As DAO.Recordset


For more info. on this topic, see this article:

ADO and DAO Library References in Access Databases
http://www.access.qbuilt.com/html/ado_and_dao.html


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html

 

[Guest]

You're right, Tom. Wouldn't want him to get a bad grade!
Bruce

 

[onedaywhen]

Why? Nobody is going to take the time to laboriously explain it to
you until you've shown that it's not counterproductive to do it with
ADO.

Ah yes, I can vouch for RON. RON leads a team of ADO programmers. They
have this one new requirement, being an existing solution that uses ADO
and unbound forms. IIRC it was done this way to perform client side
recordset processing while disconnected from the database. They don't
expect any more requests of a similar nature; merely a one off, helping
out an established client on a goodwill basis. It would be
counterproductive to use DAO: maintenance issues mainly and there is no
justification to change the design at this late stage.

Can they have their ADO answer now, please?

Jamie.

--

 

[Guest]

Jamie,

I apoligize for my "homework" comment, but having a table named "students"
and refusing to elaborate on why such an unusual approach was being taken
indicated one of those never-used-in-the-real-world questions that
programming teachers ask. Suggest that you coach Ron to provide better
explanations when submitting to the group. Most of us are willing to help if
an effort is shown on the part of the questioner.

The DAO approach I provided in my second post should translate into ADO. If
it's not what you're looking for, I suggest starting a new post with enough
details for someone else to provide a solution. Once these things get 2 or 3
days old no one ever looks at them again.

Bruce

 

[onedaywhen]

I apoligize for my "homework" comment, but having a table named "students"
and refusing to elaborate on why such an unusual approach was being taken
indicated one of those never-used-in-the-real-world questions that
programming teachers ask.

No worries. You're probably right!

Jamie.

--

 

[Larry Linson]

Ron "vouched for himself," in a post in this newsgroup, with the Subject
"ADO need help with this assignment? Help." on 10/31/2006 at 7:43PM in which
he stated it was, indeed, for a homework assignment.

Larry Linson

 

[onedaywhen]

Ron "vouched for himself," in a post in this newsgroup, with the Subject
"ADO need help with this assignment? Help." on 10/31/2006 at 7:43PM in which
he stated it was, indeed, for a homework assignment.

Oh, *that* RON. Yes, he's a no good cheat <g>.

On a totally unrelated matter, I wonder how many people get admonished
around here about having 'preconceived ideas' when the requested
technology is DAO?

Jamie.

--