Why won't this work..?

[Nightowl4933]

I have a form for users to enter data which is saved to a table, but the dta in one of the fields isn't being copyied over, and I can't see why.

tblApplications has these fields:
Location - (Data Type = Text, Field Size = 255, Format = @, Required = No, Allow Zero Length = Yes, Indexed = No, Unicode = No, IME Control = No Control, IME Sentence Mode = None and Text ALign = General)
Proposal - (Exactly the same as Location)

frmApplications has these fields
txtNewLocation and txtNewProposal, both of which are text fields, and I use the folllowing in the Click event procedure for the CommandButton:

Dim strNewLocation As Variant
Dim strNewProposal As Variant

...use these commands to get their respective values:

strNewLocation = txtNewLocation.Value
strNewPoposal = txtNewProposal.Value

...and then update the record using AddNew (having predefined everything) with the following lines:

rstTableName.AddNew![Location] = strNewLocation
rstTableName.AddNew![Proposal] = strNewProposal

Every field in the form is copied to the table, except txtNewProposal, and I really can't see why when every other field (and there are only 14 in total) does get copied . The value is definitely copied to the variable, as hovering the mouse cursor over it shows it's value.

Any ideas, please?

Thanks

 

[stopher]

.value isn't necessarily the current contents of a control. So what happens if you change .value to .text?

Also, what other code do you have? Can you post it.

 

[pbaldy]

If the code is behind a button, using the Text property will error. That code looks odd, but try

rstTableName.Update

after the proposal line.

 

[Nightowl4933]

Hi stopher,

Code, as requested. I did try to define the text data as String, but I kept getting errors until I changed it to Variant....

Private Sub cmdAddRecord_Click()

Dim dbsWhatever As DAO.Database
Dim rstTableName As DAO.Recordset

Dim strNewAuthority As Variant
Dim strNewReference As Variant
Dim strNewLocation As Variant
Dim strNewAddress1 As Variant
Dim strNewAddress2 As Variant
Dim strNewTown As Variant
Dim strNewPostCode As Variant
Dim strNewProposal As Variant
Dim strNewPremID As Variant
Dim dteNewApplicationDate As Date
Dim dblNewEasting As Double
Dim dblNewNorthing As Double
Dim dblNewCommercial As Double
Dim dblNewDwellings As Double
Dim mResponse, mString

Set dbsWhatever = CurrentDb
Set rstTableName = dbsWhatever.OpenRecordset("tblAllApplications")

strNewAuthority = cboNewAuthority.Value
strNewReference = txtNewReference.Value
strNewLocation = txtNewLocation.Value
strNewAddress1 = txtNewAddress1.Value
strNewAddress2 = txtNewAddress2.Value
strNewTown = txtNewTown.Value
strNewPostCode = txtNewPostCode.Value
strNewPoposal = txtNewProposal.Value - D'oh!
strNewPremID = txtNewPremID.Value
dteNewApplicationDate = txtNewApplicationDate.Value
dblNewEasting = txtNewEasting.Value
dblNewNorthing = txtNewNorthing.Value
dblNewCommercial = txtNewCommercial.Value
dblNewDwellings = txtNewDwellings.Value

With rstTableName
.AddNew
![PlanningAuthority] = strNewAuthority
![Initial Planning Application Reference] = strNewReference
![Date of Application] = dteNewApplicationDate
![Location] = strNewLocation
![Address 1] = strNewAddress1
![Address 2] = strNewAddress2
![Town] = strNewTown
![Post Code] = strNewPostCode
![Easting] = dblNewEasting
![Northing] = dblNewNorthing
![Commercial (m2)] = dblNewCommercial
![Dwellings] = dblNewDwellings
![Proposal] = strNewProposal
![Premises Reference] = strNewPremID
End With

If Me.chkNewCondition = True Then
rstTableName![Planning Condition Applied] = True
End If

rstTableName.Update

mResponse = MsgBox("...add more records?" & vbCrLf & _
"* Click 'Yes' to continue with this list," & vbCrLf & _
"* Click 'No' to start a new planning list or," & vbCrLf & _
"* Click 'Cancel' to return to the main screen", _
vbYesNoCancel, "Do you want to continue to...")

If mResponse = vbYes Then

With Me
.txtNewReference = ""
.txtNewLocation = ""
.txtNewAddress1 = ""
.txtNewAddress2 = ""
.txtNewTown = ""
.txtNewPostCode = ""
.txtNewEasting = 0
.txtNewNorthing = 0
.txtNewCommercial = 0
.txtNewDwellings = 0
.txtNewProposal = ""
.txtNewPremID = ""
.chkNewCondition = False
End With

ElseIf mResponse = vbNo Then

With Me
.cboNewAuthority = ""
.txtNewApplicationDate = Now()
.txtNewReference = ""
.txtNewLocation = ""
.txtNewAddress1 = ""
.txtNewAddress2 = ""
.txtNewTown = ""
.txtNewPostCode = ""
.txtNewEasting = 0
.txtNewNorthing = 0
.txtNewCommercial = 0
.txtNewDwellings = 0
.txtNewProposal = ""
.txtNewPremID = ""
.chkNewCondition = False
End With

ElseIf mResponse = vbCancel Then

DoCmd.Close

Forms("frmSwitchboard").Visible = True

End If

End Sub

 

[pbaldy]

Check your spelling.

 

[pbaldy]

Oh, and this would have alerted you:

http://www.baldyweb.com/OptionExplicit.htm

 

[arnelgp]

yes check your spelling:

strNewPoposal = txtNewProposal.Value

should be:
strNewProposal= txtNewProposal.Value

 

[Nightowl4933]

Well, I'm glad you all noticed my deliberate mistake!

I definitely need a new pair of glasses.

Cheers,

Pete

 

[pbaldy]

Sorry, I didn't realize it had to be explicitly spelled out.

 

[Nightowl4933]

Oh, and this would have alerted you:

http://www.baldyweb.com/OptionExplicit.htm

Done that!

Thank you for the heads up!

Pete

 

[Nightowl4933]

Sorry, I didn't realize it had to be explicitly spelled out.

You didn't! I'm embarrassed enough, anyway!