converting data

[helenpostle]

I have a form that has field which runs a query to find the highest record number in a table and shows that number. I then want to assign this value as a number, to another field on the form when I click on a button but it keeps coming up with an error. I assume this is because the value in the field, returned from the query is not a number. I'm stuck now, I've tried a few things but nothing seems to work

 

[helenpostle]

I saw another posting here about using a text box with the default value set to the query, I have tried this and nothing shows in the text box at all. The default value I have is =[qrySampleNo]![Expr2].

The other field I tried was a listbox field with the row source set to SELECT Max([sampleNo]+1)as expr2 This shows up with the correct value but if I try to assign this value to another field, it comes up with an empty field.

 

[Fizzio]

Post what code you have used for the button click. Without looking at it, as part of the on_click event, you could use

me.NewField = me.Oldfield.Value which should set the new control value to that of the existing one. Make sure that your formats are the same for both controls and fields though.

HTH

 

[helenpostle]

My one field is called NewSampleNo and its a listbox with the row source set to SELECT Max(sampleNo+1)as Expr2

my other field is called sampleNo and is a textbox

my button has the code:

me!sampleNo = me!NewSampleNo

when I click on the button, I get an error message saying "the field 'Samples.sampleNo' can't contain a null value

 

[Fizzio]

Is the listbox supplying a number of values or just the one. If it supplying a number of values, you could use on the doubleclick event of the listbox

me.textboxname = me.listboxname.Value

This will copy the first column value into the textbox.

 

[helenpostle]

that does work but I don't want to do it that way. The listbox with the new number in will be hidden. This is to be like an autonumber but I can't use auto-number as it has to be changeable.

Why doesn't the same code work on a button?

 

[Fizzio]

Why are using a listbox to store just 1 value - was that a typo?

Put the same code on the button (on the same form as the listbox)

me.textboxname = me.listboxname.Value

It should work fine. I've tried it here and it works no problem.

 

[helenpostle]

I got the button to work, I deleted the code and copied the code I used for the double click (which was exactly the same) and then it worked?? very strange.

I am using a listbox as that was the only control I could get to work, the control source is the result of a query, if I use a text box with the default value set to the query result as I've seen suggested elsewhere, all I get in the field is #Name?