Recordsets and Tables (1 Viewer)

[chrisjrice]

I am trying to retrieve the contents of a field in a table into a text field which I have defined in VB. The problem I am having is actually doing this! I know a little about recordsets but could do with an example of seting a record set and retrieveing a specific field from a table. Unless anyone has an easier way??

 

[Nero]

You need to use ADO method.
In your references you need to tick the 'Active X Data Objects' library
You can then use the ADODC control to access your database.
You need to use some code like this on the Form Load event:


Private Sub Form_Load()

Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection

Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data_ Source=C:\YourDB.mdb;"
.Open

End With

Set rs = New ADODB.Recordset
rs.Open "Table name", cn, adOpenKeyset, adLockOptimistic, adCmdTable

With Adodc1
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\yourDb.mdb;"
.CommandType = adCmdTable
.RecordSource = "Your table"
.Refresh
End With
End Sub

You then need to put a text box on your form
Set its data source to ADODC1 and set its data field to the field you want to look at.

If you database is secured you will need to add the system.mdw folder to the connection string.
Post back if you need more help.

[This message has been edited by Nero (edited 04-17-2002).]

[This message has been edited by Nero (edited 04-17-2002).]

 

[chrisjrice]

What I am trying to do is set up a pre-defined e-mail to be sent. I want to retrieve individual fields from a table into a string so that I can use that string in the message text of the e-mail.

For example I have a field which contains a registration number 'HT1002'.

In my message text I want to say "Your item has been assigned number 'HT002'. The HT002 text will have been retrieved from the current active record on the database.

This e-mail is activated from a form.

Hope this is not to confusing. And thanks for your RS advice, but I think that is doing somthing slightly different to what I am trying to do.

 

[Nero]

Mmm.
I see what I can come up with.
Perhaps Bukhix might have some ideas....
He's pretty cool with email.

[This message has been edited by Nero (edited 04-17-2002).]

 

[Nero]

Sorry
I get what you are trying to do now (Duh)
When you said VB I thought you were trying to connect to your database from VB.
I take it you mean VBA.
Look in the General forum for a 'Mailing List'post by Expublish.
It might give you some help.

 

[chrisjrice]

Thanks very much for your help. I managed to get all what i needed from that mailing list post. I do have one other problem, I am using the DoCmd Sendobject function to send the e-mail. This works fine when i first open the form. If i then close the form and then go back into the form the do cmd send object does not work. I have to close the database and reload the database to get it to work. I have put it through debug and the command does execute, it just seems to do nothing. Can you help?

 

[Nero]

Sounds like a refresh or update problem.
Try
'Me.Refresh'
In your code.