Help with Code - out of time please.

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Hi,

I have this below code to generate automatic recipt
number by taking the first one and then increment it at
the next data entry.

Type of the feild in the tblSeries is Number and Long
Integer.

I have put this code in the before Insert property of my
data entry form but on entering any data I get data
mismatch with rst highlighted, or the .Edit highlighted.
I have the Microsoft DAO 3.6 engine checked as well.

This code has been working in my other database verywell.

Please Help.

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim rst As Recordset, db As Database
Dim lngNextNumber As Long
Set db = CurrentDb

'Open tblSeries, lock, read next number, increment,
'update and unlock.
Set rst = db.OpenRecordset("tblSeries", , dbDenyRead)
With rst
.MoveFirst
.Edit

lngNextNumber = ![NextNumber]
![NextNumber] = lngNextNumber + 1
.Update

End With
Set db = Nothing
End Sub

Regards,

Mike
 
You probably still have the ADODB library checked as a reference, and it is
in the References list as a higher priority than the DAO library. Either
deselect it as a reference and/or disambiguate the Dim as Recordset line
(both libraries have Recordset objects):

Dim rst As DAO.Recordset
 
Thanks for responding so promptly. Should I replace the
Dim statement in my code with the one you have listed as
below?:
Dim rst As DAO.Recordset


REgards,

Mike
-----Original Message-----
You probably still have the ADODB library checked as a reference, and it is
in the References list as a higher priority than the DAO library. Either
deselect it as a reference and/or disambiguate the Dim as Recordset line
(both libraries have Recordset objects):

Dim rst As DAO.Recordset



--
Ken Snell
<MS ACCESS MVP>


Mike said:
Hi,

I have this below code to generate automatic recipt
number by taking the first one and then increment it at
the next data entry.

Type of the feild in the tblSeries is Number and Long
Integer.

I have put this code in the before Insert property of my
data entry form but on entering any data I get data
mismatch with rst highlighted, or the .Edit highlighted.
I have the Microsoft DAO 3.6 engine checked as well.

This code has been working in my other database verywell.

Please Help.

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim rst As Recordset, db As Database
Dim lngNextNumber As Long
Set db = CurrentDb

'Open tblSeries, lock, read next number, increment,
'update and unlock.
Set rst = db.OpenRecordset("tblSeries", , dbDenyRead)
With rst
.MoveFirst
.Edit

lngNextNumber = ![NextNumber]
![NextNumber] = lngNextNumber + 1
.Update

End With
Set db = Nothing
End Sub

Regards,

Mike
Click to expand...


.
Click to expand...
 
Just insert DAO. in front of the Recordset in your current Dim
statement.

--
Ken Snell
<MS ACCESS MVP>

Mike said:
Thanks for responding so promptly. Should I replace the
Dim statement in my code with the one you have listed as
below?:
Dim rst As DAO.Recordset


REgards,

Mike
-----Original Message-----
You probably still have the ADODB library checked as a reference, and it is
in the References list as a higher priority than the DAO library. Either
deselect it as a reference and/or disambiguate the Dim as Recordset line
(both libraries have Recordset objects):

Dim rst As DAO.Recordset



--
Ken Snell
<MS ACCESS MVP>


Mike said:
Hi,

I have this below code to generate automatic recipt
number by taking the first one and then increment it at
the next data entry.

Type of the feild in the tblSeries is Number and Long
Integer.

I have put this code in the before Insert property of my
data entry form but on entering any data I get data
mismatch with rst highlighted, or the .Edit highlighted.
I have the Microsoft DAO 3.6 engine checked as well.

This code has been working in my other database verywell.

Please Help.

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim rst As Recordset, db As Database
Dim lngNextNumber As Long
Set db = CurrentDb

'Open tblSeries, lock, read next number, increment,
'update and unlock.
Set rst = db.OpenRecordset("tblSeries", , dbDenyRead)
With rst
.MoveFirst
.Edit

lngNextNumber = ![NextNumber]
![NextNumber] = lngNextNumber + 1
.Update

End With
Set db = Nothing
End Sub

Regards,

Mike
Click to expand...


.
Click to expand...
Click to expand...
 
ken,

Thanks for your help, that was it, I cleared another DAO
which was checked and that took care of the problem.
I have a different question? these numbers enerated gets
saved into a table. I made a query based on that table
and made a combo box based on that query to be put on the
form and it works but the user doesn't like this combo
box and asking for a text box to show this ReciptNumber,
I couldn't do this. Can this be accomplished?

My combo box shows a zero and then the number, can I make
it to show only the number?
Thanks,

Mike
-----Original Message-----
Just insert DAO. in front of the Recordset in your current Dim
statement.

--
Ken Snell
<MS ACCESS MVP>

Mike said:
Thanks for responding so promptly. Should I replace the
Dim statement in my code with the one you have listed as
below?:
Dim rst As DAO.Recordset


REgards,

Mike
-----Original Message-----
You probably still have the ADODB library checked as a reference, and it is
in the References list as a higher priority than the
Click to expand...
DAO
library. Either
deselect it as a reference and/or disambiguate the Dim as Recordset line
(both libraries have Recordset objects):

Dim rst As DAO.Recordset



--
Ken Snell
<MS ACCESS MVP>


Hi,

I have this below code to generate automatic recipt
number by taking the first one and then increment it at
the next data entry.

Type of the feild in the tblSeries is Number and Long
Integer.

I have put this code in the before Insert property
Click to expand...
of
my
data entry form but on entering any data I get data
mismatch with rst highlighted, or the .Edit highlighted.
I have the Microsoft DAO 3.6 engine checked as well.

This code has been working in my other database verywell.

Please Help.

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim rst As Recordset, db As Database
Dim lngNextNumber As Long
Set db = CurrentDb

'Open tblSeries, lock, read next number, increment,
'update and unlock.
Set rst = db.OpenRecordset("tblSeries", , dbDenyRead)
With rst
.MoveFirst
.Edit

lngNextNumber = ![NextNumber]
![NextNumber] = lngNextNumber + 1
.Update

End With
Set db = Nothing
End Sub

Regards,

Mike


.
Click to expand...
Click to expand...


.
Click to expand...
 
RE: the combo box.... if I understand correctly, the row source query for
this combo box is returning two fields to the combo box, and your user wants
to see just the second field, not the first one?

If this is yes, change the column widths property of the combo box to set
the width of the first column to 0 ( something like 0";1" ).

If however, you want to display on the form both values, then you can use a
textbox to get the value of the second column and display that. See The
Access Web for how to do this:
http://www.mvps.org/access/forms/frm0058.htm

--
Ken Snell
<MS ACCESS MVP>



Mike said:
ken,

Thanks for your help, that was it, I cleared another DAO
which was checked and that took care of the problem.
I have a different question? these numbers enerated gets
saved into a table. I made a query based on that table
and made a combo box based on that query to be put on the
form and it works but the user doesn't like this combo
box and asking for a text box to show this ReciptNumber,
I couldn't do this. Can this be accomplished?

My combo box shows a zero and then the number, can I make
it to show only the number?
Thanks,

Mike
-----Original Message-----
Just insert DAO. in front of the Recordset in your current Dim
statement.

--
Ken Snell
<MS ACCESS MVP>

Mike said:
Thanks for responding so promptly. Should I replace the
Dim statement in my code with the one you have listed as
below?:
Dim rst As DAO.Recordset


REgards,

Mike

-----Original Message-----
You probably still have the ADODB library checked as a
reference, and it is
in the References list as a higher priority than the DAO
library. Either
deselect it as a reference and/or disambiguate the Dim
as Recordset line
(both libraries have Recordset objects):

Dim rst As DAO.Recordset



--
Ken Snell
<MS ACCESS MVP>


Hi,

I have this below code to generate automatic recipt
number by taking the first one and then increment it at
the next data entry.

Type of the feild in the tblSeries is Number and Long
Integer.

I have put this code in the before Insert property of
my
data entry form but on entering any data I get data
mismatch with rst highlighted, or the .Edit
highlighted.
I have the Microsoft DAO 3.6 engine checked as well.

This code has been working in my other database
verywell.

Please Help.

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim rst As Recordset, db As Database
Dim lngNextNumber As Long
Set db = CurrentDb

'Open tblSeries, lock, read next number, increment,
'update and unlock.
Set rst = db.OpenRecordset("tblSeries", , dbDenyRead)
With rst
.MoveFirst
.Edit

lngNextNumber = ![NextNumber]
![NextNumber] = lngNextNumber + 1
.Update

End With
Set db = Nothing
End Sub

Regards,

Mike


.
Click to expand...


.
Click to expand...
Click to expand...
 
Thanks Ken,

Re: the Combo box, I only show one but by default it
shows zero until I use the arrow to choose the number
that I want. I want it to only show the number and not
the zero.

Regards,

Mike
-----Original Message-----
RE: the combo box.... if I understand correctly, the row source query for
this combo box is returning two fields to the combo box, and your user wants
to see just the second field, not the first one?

If this is yes, change the column widths property of the combo box to set
the width of the first column to 0 ( something like 0";1" ).

If however, you want to display on the form both values, then you can use a
textbox to get the value of the second column and display that. See The
Access Web for how to do this:
http://www.mvps.org/access/forms/frm0058.htm

--
Ken Snell
<MS ACCESS MVP>



Mike said:
ken,

Thanks for your help, that was it, I cleared another DAO
which was checked and that took care of the problem.
I have a different question? these numbers enerated gets
saved into a table. I made a query based on that table
and made a combo box based on that query to be put on the
form and it works but the user doesn't like this combo
box and asking for a text box to show this ReciptNumber,
I couldn't do this. Can this be accomplished?

My combo box shows a zero and then the number, can I make
it to show only the number?
Thanks,

Mike
-----Original Message-----
Just insert DAO. in front of the Recordset in
Click to expand...
your
current Dim
statement.

--
Ken Snell
<MS ACCESS MVP>

Thanks for responding so promptly. Should I replace the
Dim statement in my code with the one you have
Click to expand...
listed
as
below?:
Dim rst As DAO.Recordset


REgards,

Mike

-----Original Message-----
You probably still have the ADODB library checked as a
reference, and it is
in the References list as a higher priority than
Click to expand...
the
DAO
library. Either
deselect it as a reference and/or disambiguate the Dim
as Recordset line
(both libraries have Recordset objects):

Dim rst As DAO.Recordset



--
Ken Snell
<MS ACCESS MVP>


Hi,

I have this below code to generate automatic recipt
number by taking the first one and then increment it at
the next data entry.

Type of the feild in the tblSeries is Number and Long
Integer.

I have put this code in the before Insert
Click to expand...
property
of
my
data entry form but on entering any data I get data
mismatch with rst highlighted, or the .Edit
highlighted.
I have the Microsoft DAO 3.6 engine checked as well.

This code has been working in my other database
verywell.

Please Help.

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim rst As Recordset, db As Database
Dim lngNextNumber As Long
Set db = CurrentDb

'Open tblSeries, lock, read next number, increment,
'update and unlock.
Set rst = db.OpenRecordset("tblSeries", , dbDenyRead)
With rst
.MoveFirst
.Edit

lngNextNumber = ![NextNumber]
![NextNumber] = lngNextNumber + 1
.Update

End With
Set db = Nothing
End Sub

Regards,

Mike


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Are you saying that you want the combo box to be blank when you first see it
until the user selects an item from the combo box? If yes, then I'm guessing
that you have a default value set for the combo box. Delete the default
value expression.

If this isn't what you have, then I'll need more info (remember - I cannot
see your setup):

Post the SQL of the Row Source query, the expression for the Default Value,
the expression for the Bound Column, the expression for the Column Widths,
and the expression for the Column Count.


--
Ken Snell
<MS ACCESS MVP>

Mike said:
Thanks Ken,

Re: the Combo box, I only show one but by default it
shows zero until I use the arrow to choose the number
that I want. I want it to only show the number and not
the zero.

Regards,

Mike
-----Original Message-----
RE: the combo box.... if I understand correctly, the row source query for
this combo box is returning two fields to the combo box, and your user wants
to see just the second field, not the first one?

If this is yes, change the column widths property of the combo box to set
the width of the first column to 0 ( something like 0";1" ).

If however, you want to display on the form both values, then you can use a
textbox to get the value of the second column and display that. See The
Access Web for how to do this:
http://www.mvps.org/access/forms/frm0058.htm

--
Ken Snell
<MS ACCESS MVP>



Mike said:
ken,

Thanks for your help, that was it, I cleared another DAO
which was checked and that took care of the problem.
I have a different question? these numbers enerated gets
saved into a table. I made a query based on that table
and made a combo box based on that query to be put on the
form and it works but the user doesn't like this combo
box and asking for a text box to show this ReciptNumber,
I couldn't do this. Can this be accomplished?

My combo box shows a zero and then the number, can I make
it to show only the number?
Thanks,

Mike
-----Original Message-----
Just insert DAO. in front of the Recordset in your
current Dim
statement.

--
Ken Snell
<MS ACCESS MVP>

Thanks for responding so promptly. Should I replace the
Dim statement in my code with the one you have listed
as
below?:
Dim rst As DAO.Recordset


REgards,

Mike

-----Original Message-----
You probably still have the ADODB library checked as a
reference, and it is
in the References list as a higher priority than the
DAO
library. Either
deselect it as a reference and/or disambiguate the Dim
as Recordset line
(both libraries have Recordset objects):

Dim rst As DAO.Recordset



--
Ken Snell
<MS ACCESS MVP>


Hi,

I have this below code to generate automatic recipt
number by taking the first one and then increment
it at
the next data entry.

Type of the feild in the tblSeries is Number and
Long
Integer.

I have put this code in the before Insert property
of
my
data entry form but on entering any data I get data
mismatch with rst highlighted, or the .Edit
highlighted.
I have the Microsoft DAO 3.6 engine checked as well.

This code has been working in my other database
verywell.

Please Help.

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim rst As Recordset, db As Database
Dim lngNextNumber As Long
Set db = CurrentDb

'Open tblSeries, lock, read next number, increment,
'update and unlock.
Set rst = db.OpenRecordset("tblSeries", ,
dbDenyRead)
With rst
.MoveFirst
.Edit

lngNextNumber = ![NextNumber]
![NextNumber] = lngNextNumber + 1
.Update

End With
Set db = Nothing
End Sub

Regards,

Mike


.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top