Posted Second time-please help-Need to meet deadline

[Mike]

HI,

I have the form frmPayments bounded to tblBook which I
use to enter records into tblBook. the data or records
entered are the recipts we have given to our customers
for payments received. We also have two recipt books
that has different recipt numbers. I have used codes to
generate the recipt numbers, We enter the first recipt
number and the code increment it by one for the next
recipt. The numbers generated by this code gets saved
(only one number at all times) into a tblSeries. I have
created a query based on this tblSeries and made a combo
box on frmPayments to show recipt number and have it
saved into tblBook. My users have been complaning that
the down arrow of the combo box is tricking them and they
always click on it but they don't need to do this since
there is always one number there. I want to change that
combo box to a text box which doesn't have the arrow
(dropdown specific to combos) but since the frmPayments
is bounded to tblBook I get error on the text box.

I tried to make the query with both the tblSeries and
tblBook but no data shows in there. when I remove tblBook
from the query, it works fine.

Is there a way around this problem so that I only have a
text box showing the recipt number and not the combo box?

Thank you in advance for your help.

Mike

 

[Niklas Östergren]

Hi Mike!

I don´t know if I´m out of line here but have you tryed to just right_click
on the combobox and to chanhe it to a textbox?

Or how about creating a new texbox and setting the rowsource for that
textbox to the same as for the combobox?

If you´r having some code in the AfterUpdate_Event for the combobox you may
need to copy some part of this code into the form´s Current_Event and set
the result to be displayed in the texbox instead of the combobox.

How this is going to be done depend´s on if you have any code and how it
looks. But general speaking you use:
Me.txtNameOnYourTextBox =
"WhatEverExpresionOrCodeUsedToDisplayTheCorrectData".

Maby you need to temperarily remove the relationship between tblBook and
other tables when trying any of these (above).

Is your db splitted into frontend/backend? If so make sure you´r the only
one that have opened the frontend db when makin this design changes. If it´s
NOT splitted still make sure you have explicit right to the db (you´r the
only one having it opened).

This is my two cent of knowlede about this. Unfortunately I don´t have more
time today but I´ll look into this formul later this eavning!

// Niklas

 

[Mike]

Thaks Niklas,

Here is the code that generates the number:

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

I had tried to change the combo box to a text box but I
get the Name error. The issue is that I want to have this
number saved in my tblBook as well. The numbers gets
saved in tblSeries and then a query shows the number and
the combo box shows it on the form and saves it in
tblBook.

All I am trying to do is to have it displyed in a text
box instead of the combo box. Unfortunately I couldn't
follow your solution and thats due to my low and moger
knowledge of this VB I guess.

When youhave time, I certainly appreciate if you colud
take a look at this issue again.

Regards,

Mike

-----Original Message-----
Hi Mike!

I don´t know if I´m out of line here but have you tryed to just right_click
on the combobox and to chanhe it to a textbox?

Or how about creating a new texbox and setting the rowsource for that
textbox to the same as for the combobox?

If you´r having some code in the AfterUpdate_Event for the combobox you may
need to copy some part of this code into the form´s Current_Event and set
the result to be displayed in the texbox instead of the combobox.

How this is going to be done depend´s on if you have any code and how it
looks. But general speaking you use:
Me.txtNameOnYourTextBox =
"WhatEverExpresionOrCodeUsedToDisplayTheCorrectData".

Maby you need to temperarily remove the relationship between tblBook and
other tables when trying any of these (above).

Is your db splitted into frontend/backend? If so make sure you´r the only
one that have opened the frontend db when makin this design changes. If it´s
NOT splitted still make sure you have explicit right to the db (you´r the
only one having it opened).

This is my two cent of knowlede about this.

Unfortunately I don´t have more

 

[Niklas Östergren]

Hi Mike!

I´ll leave soon! ;-)

Well first of all open up the form in design mode. Right click the combobox.
Select "Change too/Textbox" (I´m not 100% sure about the english words here
because I run a swedish version of Access).

That should now change your combobox control to a ordinary textcontrol,
which is what you wanted, right?

Now, before saving and closing your form make sure that the name on this
textbox havn´t changed (it shouldn´t have). And that i have exacly the same
name as the combobox had .

This is quit easy. Rightclick the newly converted combobox (now beeing a
textbox) select "Properties" (at the very bottom). Select tab "All" at the
top you have a field "Name" make sure the name there is the same as you had
on the combobox. It should be (acording to your code) [NextNumber]. Without
the brackets!

Now the bounding part!

As loong as the control, no matter if its a combobox or textbox, is bound to
a field in the table where you want to save the value you only have to edit
(type in or set a value in VBA code) to the control, svae the data (which is
done as soon as you set focus to another control). Then the value is saved
in the underlying table. If the form´s recordsource is a query instead of a
single table it works exacly the same since a SELECT query is only a set of
tables beeing joined.

Make therfore sure that your newly created textbox have the correct
rowsourse set.

Open up the form in designmode again (if you have closed it). Right click
the textbox and yet again select "Propertie". Now select tab "Data" at the
very top you have the field for the rowsource. Select the field in which you
want the data in your textbox to be saved. Save the changes and close the
form.

That should be it!

// Niklas
(Now I´m definately gone for the day!)


"Mike" <[email protected]> skrev i meddelandet
Thaks Niklas,

Here is the code that generates the number:

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

I had tried to change the combo box to a text box but I
get the Name error. The issue is that I want to have this
number saved in my tblBook as well. The numbers gets
saved in tblSeries and then a query shows the number and
the combo box shows it on the form and saves it in
tblBook.

All I am trying to do is to have it displyed in a text
box instead of the combo box. Unfortunately I couldn't
follow your solution and thats due to my low and moger
knowledge of this VB I guess.

When youhave time, I certainly appreciate if you colud
take a look at this issue again.

Regards,

Mike

-----Original Message-----
Hi Mike!

I don´t know if I´m out of line here but have you tryed to just right_click
on the combobox and to chanhe it to a textbox?

Or how about creating a new texbox and setting the rowsource for that
textbox to the same as for the combobox?

If you´r having some code in the AfterUpdate_Event for the combobox you may
need to copy some part of this code into the form´s Current_Event and set
the result to be displayed in the texbox instead of the combobox.

How this is going to be done depend´s on if you have any code and how it
looks. But general speaking you use:
Me.txtNameOnYourTextBox =
"WhatEverExpresionOrCodeUsedToDisplayTheCorrectData".

Maby you need to temperarily remove the relationship between tblBook and
other tables when trying any of these (above).

Is your db splitted into frontend/backend? If so make sure you´r the only
one that have opened the frontend db when makin this design changes. If it´s
NOT splitted still make sure you have explicit right to the db (you´r the
only one having it opened).

This is my two cent of knowlede about this.

Unfortunately I don´t have more

 

[Mike]

Hi Niklas,

Thanks again. I followed your insructon but itdoesn't
gove me then number that the code is generating insead I
get zero.

The query has the number but he the text box on the form.

Is there any other way to have these numbers generated?
i.e. entering the forst number in the text box for the
forst record and then it increment it by one for the next
record and so on?

Regards,

Mike

-----Original Message-----
Hi Mike!

I´ll leave soon! ;-)

Well first of all open up the form in design mode. Right click the combobox.
Select "Change too/Textbox" (I´m not 100% sure about the english words here
because I run a swedish version of Access).

That should now change your combobox control to a ordinary textcontrol,
which is what you wanted, right?

Now, before saving and closing your form make sure that the name on this
textbox havn´t changed (it shouldn´t have). And that i have exacly the same
name as the combobox had .

This is quit easy. Rightclick the newly converted combobox (now beeing a
textbox) select "Properties" (at the very bottom). Select tab "All" at the
top you have a field "Name" make sure the name there is the same as you had
on the combobox. It should be (acording to your code) [NextNumber]. Without
the brackets!

Now the bounding part!

As loong as the control, no matter if its a combobox or textbox, is bound to
a field in the table where you want to save the value you only have to edit
(type in or set a value in VBA code) to the control, svae the data (which is
done as soon as you set focus to another control). Then the value is saved
in the underlying table. If the form´s recordsource is a query instead of a
single table it works exacly the same since a SELECT query is only a set of
tables beeing joined.

Make therfore sure that your newly created textbox have the correct
rowsourse set.

Open up the form in designmode again (if you have closed it). Right click
the textbox and yet again select "Propertie". Now select tab "Data" at the
very top you have the field for the rowsource. Select the field in which you
want the data in your textbox to be saved. Save the changes and close the
form.

That should be it!

// Niklas
(Now I´m definately gone for the day!)


"Mike" <[email protected]> skrev i meddelandet
Thaks Niklas,

Here is the code that generates the number:

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

I had tried to change the combo box to a text box but I
get the Name error. The issue is that I want to have this
number saved in my tblBook as well. The numbers gets
saved in tblSeries and then a query shows the number and
the combo box shows it on the form and saves it in
tblBook.

All I am trying to do is to have it displyed in a text
box instead of the combo box. Unfortunately I couldn't
follow your solution and thats due to my low and moger
knowledge of this VB I guess.

When youhave time, I certainly appreciate if you colud
take a look at this issue again.

Regards,

Mike

-----Original Message-----
Hi Mike!

I don´t know if I´m out of line here but have you tryed to just right_click
on the combobox and to chanhe it to a textbox?

Or how about creating a new texbox and setting the rowsource for that
textbox to the same as for the combobox?

If you´r having some code in the AfterUpdate_Event for the combobox you may
need to copy some part of this code into the form´s Current_Event and set
the result to be displayed in the texbox instead of the combobox.

How this is going to be done depend´s on if you have any code and how it
looks. But general speaking you use:
Me.txtNameOnYourTextBox =
"WhatEverExpresionOrCodeUsedToDisplayTheCorrectData".

Maby you need to temperarily remove the relationship between tblBook and
other tables when trying any of these (above).

Is your db splitted into frontend/backend? If so make sure you´r the only
one that have opened the frontend db when makin this design changes. If it´s
NOT splitted still make sure you have explicit right to the db (you´r the
only one having it opened).

This is my two cent of knowlede about this.

Unfortunately I don´t have more

time today but I´ll look into this formul later this eavning!

// Niklas






.

.

 

[Steve Schapel]

Mike,

Well, I can't see how you have followed Niklas's instructions, since a
textbox doesn't have a RowSource property ;-)

It seems to me that the confusion here is that the textbox should be
bound (as the combobox should also have been bound, but apparently
wasn't) to the field in the tblBook table which is your receipt number,
but you haven't mentioned the name of this field so I will assume it is
named ReceiptNo. The tblSeries table should not be part of the record
source of the form... it's apparent purpose is simply to be part of the
process of generating the next receipt number, but it does not have any
part in the process of recording/storing which receipt number is
associated with which record in the tblBook table. Hope that makes
sense. So, in your code, after you generate the next number and save it
in tblSeries (the purpose of saving it there is so it is available as
the indicator for the next record), you need to assign this value to the
ReceiptNo field in the tblBook table. So, something like this...

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim rst As DAO.Recordset
Dim lngNextNumber As Long

'Open tblSeries, lock, read next number, increment,
'update and unlock.
Set rst = CurrentDB.OpenRecordset("tblSeries", , dbDenyRead)
With rst
.Edit
![NextNumber] = ![NextNumber] + 1
.Update
Me.ReceiptNo = ![NextNumber]
.Close
End With
Set rst = Nothing
End Sub

Another way to do this, possibly simpler, would be...

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.ReceiptNo = DMax("[ReceiptNo]","tblBook")+1
End Sub

 

[Niklas Östergren]

Hmm, I guess that my swedish is better than my english. I´m using a swedish
version of Access as well which might explain bad choyse of names for the
propertie.

So what´s the proper word for a textbox rowsource? Recordsource or?
In swedish it´s "Kontrollkälla" ;-)

// Niklas

Mike,

Well, I can't see how you have followed Niklas's instructions, since a
textbox doesn't have a RowSource property ;-)

It seems to me that the confusion here is that the textbox should be
bound (as the combobox should also have been bound, but apparently
wasn't) to the field in the tblBook table which is your receipt number,
but you haven't mentioned the name of this field so I will assume it is
named ReceiptNo. The tblSeries table should not be part of the record
source of the form... it's apparent purpose is simply to be part of the
process of generating the next receipt number, but it does not have any
part in the process of recording/storing which receipt number is
associated with which record in the tblBook table. Hope that makes
sense. So, in your code, after you generate the next number and save it
in tblSeries (the purpose of saving it there is so it is available as
the indicator for the next record), you need to assign this value to the
ReceiptNo field in the tblBook table. So, something like this...

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim rst As DAO.Recordset
Dim lngNextNumber As Long

'Open tblSeries, lock, read next number, increment,
'update and unlock.
Set rst = CurrentDB.OpenRecordset("tblSeries", , dbDenyRead)
With rst
.Edit
![NextNumber] = ![NextNumber] + 1
.Update
Me.ReceiptNo = ![NextNumber]
.Close
End With
Set rst = Nothing
End Sub

Another way to do this, possibly simpler, would be...

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.ReceiptNo = DMax("[ReceiptNo]","tblBook")+1
End Sub

--
Steve Schapel, Microsoft Access MVP

Hi Niklas,

Thanks again. I followed your insructon but itdoesn't
gove me then number that the code is generating insead I
get zero.

The query has the number but he the text box on the form.

Is there any other way to have these numbers generated?
i.e. entering the forst number in the text box for the
forst record and then it increment it by one for the next
record and so on?

Regards,

Mike

 

[Steve Schapel]

Niklas,

I realised it was a language factor
A form or a report has a Record Source. A textbox has a Control Source.
A combobox also has a Control Source, which is the property that
determines which field in the form/report's underlying Record Source the
control is bound to. And as well, it also has a Row Source, which is
the property that determines where it gets its list of values from for
the drop-down list. The same applies to a listbox. Hope that clarifies.

 

[Niklas Östergren]

Thank´s Steve!

I´ll try to remember this until the next time! If I´m unsure I´ll use
swedish words instead, or? ;-)

// Niklas

 

[Mike]

Thank you you both. I followed to the place I could and
but I got confused where I didn't see the Row SOurce
propery and I thought to myself since I have changed a
combo BoX to a Text Box then the Row Source may needed to
stay and I don't know about it and that I mau have done
somethng wrong somewhere in the process.

Once again, I thank you both for your help anf efforst.

Regards,

Mike

-----Original Message-----
Mike,

Well, I can't see how you have followed Niklas's instructions, since a
textbox doesn't have a RowSource property ;-)

It seems to me that the confusion here is that the textbox should be
bound (as the combobox should also have been bound, but apparently
wasn't) to the field in the tblBook table which is your receipt number,
but you haven't mentioned the name of this field so I will assume it is
named ReceiptNo. The tblSeries table should not be part of the record
source of the form... it's apparent purpose is simply to be part of the
process of generating the next receipt number, but it does not have any
part in the process of recording/storing which receipt number is
associated with which record in the tblBook table. Hope that makes
sense. So, in your code, after you generate the next number and save it
in tblSeries (the purpose of saving it there is so it is available as
the indicator for the next record), you need to assign this value to the
ReceiptNo field in the tblBook table. So, something like this...

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim rst As DAO.Recordset
Dim lngNextNumber As Long

'Open tblSeries, lock, read next number, increment,
'update and unlock.
Set rst = CurrentDB.OpenRecordset("tblSeries", , dbDenyRead)
With rst
.Edit
![NextNumber] = ![NextNumber] + 1
.Update
Me.ReceiptNo = ![NextNumber]
.Close
End With
Set rst = Nothing
End Sub

Another way to do this, possibly simpler, would be...

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.ReceiptNo = DMax("[ReceiptNo]","tblBook")+1
End Sub

--
Steve Schapel, Microsoft Access MVP

Hi Niklas,

Thanks again. I followed your insructon but itdoesn't
gove me then number that the code is generating insead I
get zero.

The query has the number but he the text box on the form.

Is there any other way to have these numbers generated?
i.e. entering the forst number in the text box for the
forst record and then it increment it by one for the next
record and so on?

Regards,

Mike

.

 

[Mike]

Thank you you both. I followed to the place I could and
but I got confused where I didn't see the Row SOurce
propery and I thought to myself since I have changed a
combo BoX to a Text Box then the Row Source may needed to
stay and I don't know about it and that I mau have done
somethng wrong somewhere in the process.

Once again, I thank you both for your help anf efforst.

Regards,

Mike