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

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.

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

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

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.

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?

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.

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?