Unbound Form needing to add new record

  • Thread starter Thread starter Grizz
  • Start date Start date
G

Grizz

I have a unbound form, for doing calculations. The total output from user
input will show lowest price. The user will input bought price, at which the
user will click a button to add to tables. This is where I am having
problems with the coding.

Private Sub BtnBought_Click()

Dim dbLocal As Database
Dim rec As Recordset
Set dbLocal = CurrentDb()

Set rec = dbLocal.OpenRecordset("LoadInfoTbl", dbOpenDynaset)

With rec
.AddNew
!BuyDate = Me!TxtDate
!Weight = Me!TxtWeight
!Mileage = Me!TxtMileage
!Rate = Me!TxtRate
!BuyPrice = Me!TxtBuyPrice
.Update
.Close
End With
End Sub

I get an error on the button_click() highlighting the dbLocal as Database

I have tried to ommit this and set diretly in the rec =
currentDb.OpenRecordset Then i get a error on the dbOpenDynaset.

The form is Blank, no select statement to pull in tables no queries just
unbound text boxes and one combo box
the boxes are
txtDate ---------date()
txtMileage
txtRate
CmbChoice
txtYield
txtRate ..........Button will ouput buy Price
txtBuyPrice
txtTruck
txtBought ............... Button to add new record to three tables no the
fields are not the same for three tables.
 
What version of Access are you using?

First thing I would try is changing the declarations:

Dim dbLocal as DAO.Database
Dim rec as DAO.Recordset
 
Dale Fye said:
What version of Access are you using?

First thing I would try is changing the declarations:

Dim dbLocal as DAO.Database
Dim rec as DAO.Recordset
Click to expand...

I would do that, too, but also I suspect there is no reference set to the
DAO Object Library. If this is Access 2000, 2002, or 2003, Grizz should
click Tools -> References..., locate "Microsoft DAO 3.6 Object Library" in
the list, and put a check mark next to it. If it's Access 2007, then I
think it's the "Access Database Engine Library" or something like that, but
I can't check it at the moment.
 
Thank You, that worked great i have been scratching my head on this for a
long time on to why it wasn't working.

If i need to send to another table could i just name recSecond as anoter
recordset and code below the previous one?

and what is the final code to close down the reference to the database as
there is a "close" for the recordset? or not to worry about it
 
Thank You, that worked great, a check box not checked wow, I was scratching
my head on this for a long time. You both helped me out Thank You, I know
this is off the main topic am i going to have problems with this using myDB
for PDA? I understand this is pretty good to sync up with access.
 
Grizz said:
Thank You, that worked great, a check box not checked wow, I was
scratching
my head on this for a long time. You both helped me out Thank You, I
know
this is off the main topic am i going to have problems with this using
myDB
for PDA? I understand this is pretty good to sync up with access.
Click to expand...


I assume myDB is another database program, but I don't know anything about
it. In general, if there's an ODBC driver for a database, Access can link
to it and import its data.
 
Thank You dirk, I do believe acces syncs up real good. Yet I might be back
here again after I try to get them to work

Can you take a look at my last reply too, as i had a question that i didn't
want to duplicate over here.

Thank You again for your help
 
Grizz said:
Thank You, that worked great i have been scratching my head on this for a
long time on to why it wasn't working.

If i need to send to another table could i just name recSecond as anoter
recordset and code below the previous one?
Click to expand...

If I understand you, the answer is yes.
and what is the final code to close down the reference to the database as
there is a "close" for the recordset? or not to worry about it
Click to expand...

In this case, don't worry about it. You didn't open dbLocal, you just got a
copy of the current database from CurrentDb(). You don't want to close that
database, and Access wouldn't let you even if you tried. In general, close
what you open, don't close what you don't open.

If you had actually opened a database object, using the OpenDatabase method,
then you would close that database by calling its Close method.

I make it a habit to explicitly destroy all object variables I use by
setting them to Nothing before exiting the procedure, so I would write:

Set rec = Nothing
Set dbLocal = Nothing

.... before the End Sub statement. However, all local variables, including
object variables, are supposed to be cleaned up automatically when a
procedure exits, so that's just a bit of obsessive housekeeping on my part.
There was a time, years ago, when VBA didn't clean up as well as it was
supposed to, and one could occasionally end up with dangling object
references. At the time, it was considered safest to always explicitly set
objects to Nothing. Nowadays I am told that's not an issue, and there's no
real need for "Set <object> = Nothing". Old habits die hard, though, and
compulsive tidiness makes for safer programming.
 
Back
Top