Update Table from Form

[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---

 

[Alex Dybenko]

Hi,
at what line do you get this compile error? Make sure that you have a
reference to Microsoft ActiveX Data objects 2.8 library, this is one where
adodb.recordset is defined

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

 

[ryguy7272]

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---

 

[Alex Dybenko]

Hi,
you have to add
Dim mydb as string

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

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---

 

[John W. Vinson]

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---

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.

 

[Steve Sanford]

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"

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

 

[ryguy7272]

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---