Keep the previous value after creating a new record (1 Viewer)

[Andy_CD]

Hi,
I have used this to code to open a new form keeping the details of one of the fields:
Private Sub Command48_Click()
Const cQuote = """"
Me!Cost_Centre.DefaultValue = cQuote & Me!Cost_Centre.Value & cQuote
DoCmd.GoToRecord , "Form Label BRI", acNewRec

End Sub

It works perfectly, however I want two other fields to behave in the same way any ideas?
I have tried this
Private Sub Command48_Click()
Const cQuote = """"
Me!Cost_Centre.DefaultValue = cQuote & Me!Cost_Centre.Value & cQuote
Const cQuote = """"
Me!Patient_Name.DefaultValue = cQuote & Me!Patient_Name.Value & cQuote
Const cQuote = """"
Me!Hospital_Number.DefaultValue = cQuote & Me!Hospital_Number.Value & cQuote
End Sub

But it freaked out at me trying to set another constant.

Any ideas peeps???

 

[MarkK]

Freaked out at you?

Your code declares the same constant three times in the same subroutine. Delete the second two declarations. You only need the first.

 

[Andy_CD]

Ok, yes I understand that but, how do I set contants for the other two fields?

 

[Gasman]

Ok, yes I understand that but, how do I set contants for the other two fields?

You do not need constants in a sub, you can just use a string

Dim strQuote as string
strQuote = """"

Where the constant comes in handy is if you declare it public at the top of your code

Public Const = """"

then it is available to all the sub routines and saves you adding those two lines of code to every sub.

 

[Andy_CD]

Thank you, that works, but how do I code it to take the data for two other fields or is that asking too much of Access?

 

[jdraw]

Andy,

Sample vba Modules, Functions and Subs

Good luck.

 

[missinglinq]

Private Sub Command48_Click()

Const cQuote = """"

 Me!Cost_Centre.DefaultValue = cQuote & Me!Cost_Centre.Value & cQuote
 Me!Patient_Name.DefaultValue = cQuote & Me!Patient_Name.Value & cQuote
 Me!Hospital_Number.DefaultValue = cQuote & Me!Hospital_Number.Value & cQuote

 DoCmd.GoToRecord , "Form Label BRI", acNewRec

End Sub

 

[Andy_CD]

Thanks but it still doesn't like it

 

[Minty]

I'm slightly confused by the need for the quotes at all - particularly if one of the values is a number. I'm sure that access will assign the value with the quotes around the string if they are required.

Try it without any of the quotes added on.

 

[Andy_CD]

I get a run-time error 438 if that helps

 

[Andy_CD]

I'm slightly confused by the need for the quotes at all - particularly if one of the values is a number. I'm sure that access will assign the value with the quotes around the string if they are required.

Try it without any of the quotes added on.

Do you mean the phrase cQuote?

 

[Minty]

Yes just try

Me.Cost_Centre.DefaultValue = Me.Cost_Centre.Value
Me.Patient_Name.DefaultValue = Me.Patient_Name.Value
Me.Hospital_Number.DefaultValue = Me.Hospital_Number.Value

 

[Andy_CD]

Thank you that has done the trick!

 

[Andy_CD]

Although next problem is it provides #Name? instead of the data in the two text fields. Should I convert these to string?

 

[Minty]

Yes apologies - I have now had a chance to test and you do need quotes around strings but not around numbers, by the same token I suspect you would need #'s around dates.

 

[Andy_CD]

ok, so would the quotes be the word quote or the mark " ?
Thank you again!

 

[Andy_CD]

SOLVED Re: Keep the previous value after creating a new record

Thanks all, I have got it now

 

[jdraw]

Although next problem is it provides #Name? instead of the data in the two text fields. Should I convert these to string?

Perhaps there is a misspelling in your names/controls?

 

[missinglinq]

The advantage of using syntax like the original

Const cQuote = """"
Me!Cost_Centre.DefaultValue = cQuote & Me!Cost_Centre.Value & cQuote

including the double-double quotes, is that the same syntax will work for Text, Number, DateTime and Boolean Datatypes. , without having to use type-specific delimiters!

Linq ;0)>

 

[missinglinq]

Sorry, have no idea how it happened but this was a double-post!