Update Table from Form

  • Thread starter Thread starter ryguy7272
  • Start date Start date
R

ryguy7272

I am working with the following code:
Private Sub Command1_Click()
Dim err As Integer
Dim cnn1 As ADODB.Connection
Dim rstPatientTable As ADODB.Recordset
Dim strCnn As String




'if no errors insert data
If err < 1 Then
' Open a connection.
Set cnn1 = New ADODB.Connection
mydb = "C:\Documents and Settings\rms\Desktop\Contacts.mdb"
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mydb
cnn1.Open strCnn

' Open contact table.
Set rstPatientTable = New ADODB.Recordset
rstPatientTable.CursorType = adOpenKeyset
rstPatientTable.LockType = adLockOptimistic
rstPatientTable.Open "PatientTable", cnn1, , , adCmdTable


'get the new record data
rstPatientTable.AddNew
rstPatientTable!FirstName = txtFirstName
rstPatientTable!LastName = txtLastName
rstPatientTable!ConsultDate = txtConsultDate
rstPatientTable!SIM_Date = txtSIM_Date
rstPatientTable!RT_STart = txtRT_Start
rstPatientTable!RT_End = txtRT_End
rstPatientTable.Update

' Show the newly added data.
MsgBox "New patient: " & rstPatientTable!FirstName & " " &
rstPatientTable!FirstName & " has been successfully added!!"


'close connections
rstPatientTable.Close
cnn1.Close


Else
MsgBox "An Error has occurred, please check and try again"
End If

End Sub


I get a message that reads 'Compile Error: User-defined type not defined'.
I have a reference set to Microsoft ADO Ext. 2.8 for DDL and Security
I can't seem to update my 'PatientTable'. What am I doing wrong?

Also, is there a way to set the DB to anyone's desktop, using a relative
reference, instead of this hard-coded reference:
mydb = "C:\Documents and Settings\rms\Desktop\Contacts.mdb"
I am planning to email this DB to a few people, once it is operational, and
the people who will receive this don't know any VBA at all.

I'd sincerely appreciate any help with this!

Thanks,
Ryan---
 
Maybe I checked off the wrong library before... Checking the reference to
'Microsoft ActiveX Data objects 2.8 library' seemed to work, but now I have a
new problem. I get a message that reads 'Compile error: variable not
defined'.

This is highlighted blue:
mydb =

I have some sample code that I found on the web; the person who posted the
code didn't Dim the 'mydb' variable. I tried dimming this as a String;
didn't work. I tried a few other things; nothing has worked so far.

I've done lots of Excel VBA work, so I feel like I know a little about this
stuff (emphasis on the 'little'). I'm kind of new to VBA in Access though.

Any thoughts?

Thanks,
Ryan---
 
Hi,
you have to add
Dim mydb as string

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com


ryguy7272 said:
Maybe I checked off the wrong library before... Checking the reference to
'Microsoft ActiveX Data objects 2.8 library' seemed to work, but now I
have a
new problem. I get a message that reads 'Compile error: variable not
defined'.

This is highlighted blue:
mydb =

I have some sample code that I found on the web; the person who posted the
code didn't Dim the 'mydb' variable. I tried dimming this as a String;
didn't work. I tried a few other things; nothing has worked so far.

I've done lots of Excel VBA work, so I feel like I know a little about
this
stuff (emphasis on the 'little'). I'm kind of new to VBA in Access
though.

Any thoughts?

Thanks,
Ryan---
Click to expand...
 
Maybe I checked off the wrong library before... Checking the reference to
'Microsoft ActiveX Data objects 2.8 library' seemed to work, but now I have a
new problem. I get a message that reads 'Compile error: variable not
defined'.

This is highlighted blue:
mydb =

I have some sample code that I found on the web; the person who posted the
code didn't Dim the 'mydb' variable. I tried dimming this as a String;
didn't work. I tried a few other things; nothing has worked so far.

I've done lots of Excel VBA work, so I feel like I know a little about this
stuff (emphasis on the 'little'). I'm kind of new to VBA in Access though.

Any thoughts?

Thanks,
Ryan---
Click to expand...

If you have Option Explicit set (which is a VERY good thing to do!!!) you must
Dim every variable. I'd suggest

Dim mydb As DAO.Database

and be sure that the DAO library is checked.
 
Hi Ryan,

It looks like you have a button on a form that runs the code to add a new
patient. Wouldn't it be easier to open a bound form (bound to the table
"PatientTable") with the Data entry property set to yes?

If you have to run code to add the patient, I would use DAO instead of ADO,
if the table "PatientTable" is in the same MDB. The DAO code is much simpler
(IMO).
Also, is there a way to set the DB to anyone's desktop, using a relative
reference, instead of this hard-coded reference:
mydb = "C:\Documents and Settings\rms\Desktop\Contacts.mdb"
Click to expand...

Yes, there is a way. If you open a DOS window and type the command SET, you
will see a list of enviroment variables. One of those is the variable
"USERPROFILE".

Put the following example code in a standard module and run it:

Public Sub dos()

Dim t As String
t = Environ("userprofile")
MsgBox t
End Sub


To use it in your code, it would look like:

Dim mydb as string
..
.. more code
..
mydb = Environ("userprofile") & "\DeskTop\Contacts.mdb"



HTH
 
Thanks Alex! Dim mydb as string -- that was it!! I could swear that I tried
this; not exactly sure what happened there but all is well now!!

Thanks for the tip Steve. I've heard about bound forms, but never used one.
I will try to research this later today. If you get a chance in the
meantime, please post a few links that would describe how I may do this in
the scenario that I described. Finally, thanks for the "USERPROFILE" trick;
I'll try it out later today.

Thanks everyone!!

Regards,
Ryan---
 
Back
Top