Question about setting db=?

[a24t42]

In my database, I have 4 forms that all have the following code
attached to a command button.

----------------------
Private Sub cmdSave_Click()
Dim testmsg As Integer
Dim db As Database
Dim rs As Recordset

Set db = OpenDatabase("D:\SouthEastern\STC_Stock\Stock.mdb")
Set rs = db.OpenRecordset("Counter", dbOpenDynaset)

'Increment the counter by 1
rs.Edit
rs!NextNumber = rs!NextNumber + 1
rs.Update

'Save the record
DoCmd.RunCommand acCmdSaveRecord

'Message Box
testmsg = MsgBox("Do you want to add another Record?", 4, "Question?")

'Add another record
If testmsg = 6 Then

DoCmd.GoToRecord , , acNewRec
Me![Date_Rcd].SetFocus

Else

DoCmd.Close

End If

End Sub
----------------------------------------------
My question is about the 'set db' line. It is requiring that I put in
the full path to the database. If I put just "Stock.mdb" I get an
error message. The code is working fine. My problem is if I move the
database to another computer, I manaully have to change the 'set db'
line on all 4 forms. Is there a way to declare it only once? Or is
there just a better way?

Thanks in advance.

 

[Ken Sheridan]

I'd suggest storing the path in a one row/one column table in the front end
file. That way its easily maintainable via a simple bound form, and can be
looked up whenever needed, e.g.

Dim strStockDb as String

strStockDb = DLookup("FullPath", "StockDbLocation")
Set db = OpenDatabase(strStockDb )

where StockDbLocation is the table name and FullPath is the column name.

Ken Sheridan
Stafford, England

 

[aaron.kempf]

dude

DO NOT USE DAO IT CAUSES CORRUPTION AND HANGS

 

[a24t42]

I assume your comment was directed at me and not Ken. I wish I knew
what you were saying. My knowledge of VB is zip. I found some code on
the Internet and adapted it to do what I wanted. It seems to be
working fine. This was the only example of code I could adapt with my
llimited knowledge. Do you have a suggestion on a better way?

Thanks.

dude

DO NOT USE DAO IT CAUSES CORRUPTION AND HANGS

In my database, I have 4 forms that all have the following code
attached to a command button.

----------------------
Private Sub cmdSave_Click()
Dim testmsg As Integer
Dim db As Database
Dim rs As Recordset

Set db = OpenDatabase("D:\SouthEastern\STC_Stock\Stock.mdb")
Set rs = db.OpenRecordset("Counter", dbOpenDynaset)

'Increment the counter by 1
rs.Edit
rs!NextNumber = rs!NextNumber + 1
rs.Update

'Save the record
DoCmd.RunCommand acCmdSaveRecord

'Message Box
testmsg = MsgBox("Do you want to add another Record?", 4, "Question?")

'Add another record
If testmsg = 6 Then

DoCmd.GoToRecord , , acNewRec
Me![Date_Rcd].SetFocus

Else

DoCmd.Close

End If

End Sub
----------------------------------------------
My question is about the 'set db' line. It is requiring that I put in
the full path to the database. If I put just "Stock.mdb" I get an
error message. The code is working fine. My problem is if I move the
database to another computer, I manaully have to change the 'set db'
line on all 4 forms. Is there a way to declare it only once? Or is
there just a better way?

Thanks in advance.

 

[aaron.kempf]

yeah.. learn ADO


DAO _HANGS_ and DAO isn't installed with windows and office and mdac
any longer.. it's going to be a PITA if you ever have to put this app
on another machine


DAO was made obsolete 10 years ago

learn the difference

I assume your comment was directed at me and not Ken. I wish I knew
what you were saying. My knowledge of VB is zip. I found some code on
the Internet and adapted it to do what I wanted. It seems to be
working fine. This was the only example of code I could adapt with my
llimited knowledge. Do you have a suggestion on a better way?

Thanks.

dude

DO NOT USE DAO IT CAUSES CORRUPTION AND HANGS

On Mar 14, 12:56 pm, (e-mail address removed) wrote:

In my database, I have 4 forms that all have the following code
attached to a command button.
----------------------
Private Sub cmdSave_Click()
Dim testmsg As Integer
Dim db As Database
Dim rs As Recordset
Set db = OpenDatabase("D:\SouthEastern\STC_Stock\Stock.mdb")
Set rs = db.OpenRecordset("Counter", dbOpenDynaset)
'Increment the counter by 1
rs.Edit
rs!NextNumber = rs!NextNumber + 1
rs.Update
'Save the record
DoCmd.RunCommand acCmdSaveRecord
'Message Box
testmsg = MsgBox("Do you want to add another Record?", 4, "Question?")
'Add another record
If testmsg = 6 Then
DoCmd.GoToRecord , , acNewRec
Me![Date_Rcd].SetFocus
Else
DoCmd.Close
End If
End Sub
----------------------------------------------
My question is about the 'set db' line. It is requiring that I put in
the full path to the database. If I put just "Stock.mdb" I get an
error message. The code is working fine. My problem is if I move the
database to another computer, I manaully have to change the 'set db'
line on all 4 forms. Is there a way to declare it only once? Or is
there just a better way?
Thanks in advance.

 

[a24t42]

Thank you Ken. Your suggestion worked!

Judy

I'd suggest storing the path in a one row/one column table in the front end
file. That way its easily maintainable via a simple bound form, and can be
looked up whenever needed, e.g.

Dim strStockDb as String

strStockDb = DLookup("FullPath", "StockDbLocation")
Set db = OpenDatabase(strStockDb )

where StockDbLocation is the table name and FullPath is the column name.

Ken Sheridan
Stafford, England

In my database, I have 4 forms that all have the following code
attached to a command button.

----------------------
Private Sub cmdSave_Click()
Dim testmsg As Integer
Dim db As Database
Dim rs As Recordset

Set db = OpenDatabase("D:\SouthEastern\STC_Stock\Stock.mdb")
Set rs = db.OpenRecordset("Counter", dbOpenDynaset)

'Increment the counter by 1
rs.Edit
rs!NextNumber = rs!NextNumber + 1
rs.Update

'Save the record
DoCmd.RunCommand acCmdSaveRecord

'Message Box
testmsg = MsgBox("Do you want to add another Record?", 4, "Question?")

'Add another record
If testmsg = 6 Then

DoCmd.GoToRecord , , acNewRec
Me![Date_Rcd].SetFocus

Else

DoCmd.Close

End If

End Sub
----------------------------------------------
My question is about the 'set db' line. It is requiring that I put in
the full path to the database. If I put just "Stock.mdb" I get an
error message. The code is working fine. My problem is if I move the
database to another computer, I manaully have to change the 'set db'
line on all 4 forms. Is there a way to declare it only once? Or is
there just a better way?

Thanks in advance.

 

[Tony Toews]

I assume your comment was directed at me and not Ken. I wish I knew
what you were saying. My knowledge of VB is zip. I found some code on
the Internet and adapted it to do what I wanted. It seems to be
working fine. This was the only example of code I could adapt with my
llimited knowledge. Do you have a suggestion on a better way?

Regretfully we wish Aaron would leave the newsgroups.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[David W. Fenton]

Regretfully we wish Aaron would leave the newsgroups.

I wish it with no regrets whatsoever and with happy anticipation of
the extreme joy for all of us the day when it finally happens.

My only regret is that that day seems to be in the future.