VBA - Run-time error '3314'

Hi guys,

I've just made some changes to my database (set a lot of the fields to Required)

I've now just got this message:

Run-time error '3314':
This field 'tblServiceHistory.VehicleUID' cannot contain a Null value because the Required property for this field is set to True. Enter a value in this field.


Which higlights this line:

Me!VehicleUID = Me.Parent.lstVehicles.Value


Out of this function:

Private Sub Form_Current()
    If Me.NewRecord = False Then
        DoCmd.RunCommand acCmdRecordsGoToNew
    End If
    Me!VehicleUID = Me.Parent.lstVehicles.Value
End Sub



What I am trying to achieve on the above function is that as soon as the user creates a new record, the VehicleUID automatically becomes the value found in Me.Parent.lstVehicles


Anybody able to help me out please?


Cheers guys 'n gals!
LVL 4
Cyber-DrugsAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
rockiroadsConnect With a Mentor Commented:
I didnt post that code!!!!!

u doing your copy/paste error trick again

Private Sub Form_Current()
    If Me.NewRecord = True Then
        Me.VehicleUID = Me.Parent.lstVehicles.Value
    End If
End Sub


Only assign vehicle id when adding a new record

0
 
Jonathan KellyCommented:
Hi there,

Is there a value in Me.Parent.lstVehicles.Value ?
0
 
Cyber-DrugsAuthor Commented:
Datrias,

There should be, as in the Parent Form being referenced I have this:

Private Sub Form_Load()
    ' Select first item in list
    If lstVehicles.ListCount > 0 Then lstVehicles.Value = lstVehicles.ItemData(1)
End Sub
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Jonathan KellyCommented:
maybe a simple tweak like this will shed some light on the subject.

  If lstVehicles.ListCount > 0 Then
    lstVehicles.Value = lstVehicles.ItemData(1)
  Else
   lstVehicles.Value = 0
  end if


also Ive never used me.parent - maybe changing it to forms!ParentFormName!lstVehicles   might help.

if the above doesnt work
I would place a breakpoint on
 Me!VehicleUID = Me.Parent.lstVehicles.Value

and have a look at Me.Parent.lstVehicles
0
 
Jonathan KellyCommented:
also

this line looks a little suspect

  If lstVehicles.ListCount > 0 Then lstVehicles.Value = lstVehicles.ItemData(1)

Is lstVehicles a listbox control ?
0
 
Cyber-DrugsAuthor Commented:
Datrias,

That's correct, it's a listbox. Basically it's loading up the form, and as soon as the form is loaded, it selects the first item in the listbox, which works perfectly. But it seems that the problem is this:

As soon as my subform loads, it tries to create a new record, rather than waiting for "new record" button to be clicked, and then assigning a value to the VehicleUID field.

Any ideas on how to tweak that?
0
 
Jonathan KellyCommented:
What is it that you want to do - exactly?
0
 
Cyber-DrugsAuthor Commented:
Basically, I want it so that when the "create new record" button in the Navigational Menu is clicked, automatically the VehicleUID field is set to the value found in me.Parent.lstVehicles.Value
0
 
Jonathan KellyCommented:
I would try this a different way

on your on Add New button click place the following code

    docmd.setwarnings off
    DoCmd.RunSQL "INSERT INTO YourSubTable (VehicleUID ) VALUES(" & Chr(34) & VehicleUID & Chr(34) & ");"
    Me.YourSubForm.Requery
    docmd.setwarnings on

This will insert a record in your table and requery the form.
0
 
rockiroadsCommented:
Hi Justin,

one would assume u have a vehicle loaded before u click the service button, yes?

Why do u want to create a new record when the form is loaded?
If u wanted to do that, then I would first tryt adding this in Form_Load, not current


Private Sub Form_Loaf()
    DoCmd.RunCommand acCmdRecordsGoToNew
    Me.VehicleUID = Me.Parent.lstVehicles.Value
End Sub


0
 
rockiroadsCommented:
ok, now thinking about it (apart from the dodgy spelling!)

the subforms will be loaded first before the vehicle is selected

so this is what I suggest, check for null then go into add

If IsNull(Me.Parent.lstVehicles.Value) = False Then
    DoCmd.RunCommand acCmdRecordsGoToNew
    Me.VehicleUID = Me.Parent.lstVehicles.Value
End If



Now if u put this in Form_Current, it means u are always in add mode
To me it doesnt make sense, Im not sure why u want to do that

What is your logic into going into addmode, is it everytime u select a vehicle?

I would not put this code to specify add a record in form_current, instead create a public funciton that can be called by the parent
e.g.

public sub Form_Current()
    If Me.NewRecord = True Then Me.VehicleUID =  Me.Parent.lstVehicles.Value
End sub


public sub GoAddMode()
    If IsNull(Me.Parent.lstVehicles.Value) = False Then
        DoCmd.RunCommand acCmdRecordsGoToNew
        Me.VehicleUID = Me.Parent.lstVehicles.Value
    End If
end sub



Now u call GoAddMode from your parent if u want it to go into addmode
if u hit the add button in the subform from the navigation bar, then that automatically goes into addmode and it calls Form_Current which should then set the vehicle ID
0
 
Leigh PurvisDatabase DeveloperCommented:
How many records are there in the listbox?
Only one perhaps?
lstVehicles.ItemData(1)
will try to set the value from the second item in the list - you realize that?  The listbox itemdata collection is zero based.
0
 
Jonathan KellyCommented:
SHOULD BE

docmd.setwarnings off
    DoCmd.RunSQL "INSERT INTO YourSubTable (VehicleUID ) VALUES(" & Chr(34) & me.Parent.lstVehicles.Value & Chr(34) & ");"
    Me.YourSubForm.Requery
    docmd.setwarnings on
0
 
Cyber-DrugsAuthor Commented:
Hi guys,

Sorry I got side-tracked for a few, I'm just going to read all recent replies now...
0
 
rockiroadsCommented:
for a few what, beers?
Have u tried Cobra?
Im going out tonite and the pub Im going to have got Cobra on draught, absolutely luvly!!! Non kroney but do have stella though
0
 
Jonathan KellyCommented:
how about Erdinger? smashing stuff !
0
 
Cyber-DrugsAuthor Commented:
Datrias (1st reply),
 It's not an actual "add new" button. It's part of MS Access on the Navigational Bar. The Arrow with an astix * on it.

rocki,
One would assume correctly.
I'm not really trying to create a new record as the form loads, so I'm probably using the wrong code. I'm trying to on creation of a new record, automatically give the VehicleUID field the value found in the listbox.

rocki (2nd reply),
Read my above response before I touch your posted code please. I don't want to be confused with another headache at the end of the day, hehe. :P

LPurvis,
There will always be a minimum of 10 records in the listbox, it will NEVER be empty, I can confirm that.



Cheers guys!
0
 
Cyber-DrugsAuthor Commented:
OK, we can discuss beer after I've solved this problem, calm down boys. :P
0
 
rockiroadsCommented:
Aint tried  Erdinger
If I ever see it, I'll give it a go
what is it lager? ale?


Justin, if u want to go into addmode via navigation bar only then just do this


public sub Form_Current()
    If Me.NewRecord = True Then Me.VehicleUID =  Me.Parent.lstVehicles.Value
End sub
0
 
Jonathan KellyCommented:
Oops - didnt catch that!

You could set the Default Propety on your form field to
= Parent.lstVehicles

or maybe
= nz(Parent.lstVehicles,"")



0
 
Cyber-DrugsAuthor Commented:
Sorry for my ignorance rocki, but "addmode" ? Can't say I've heard of that yet...
0
 
Cyber-DrugsAuthor Commented:
Hi Datrias,

Unfortunately, the elements for that table isn't on the form, the form references a load of other fields from the record though.
0
 
Cyber-DrugsAuthor Commented:
rocki,

Could you explain that "addmode"


everyone else,

Any other ideas?
0
 
Leigh PurvisDatabase DeveloperCommented:
EE-Stuff it?  Possibly one of those things that just takes a jiffy - but helps if you're sat infront of it.
0
 
Cyber-DrugsAuthor Commented:
0
 
Leigh PurvisDatabase DeveloperCommented:
Oh right.
Subforms load before the main form.  Did you know that?

Try setting the value of the subform from the mainform.
That way it's loaded and has a value when it's called.
0
 
rockiroadsCommented:
addmode thats when u go into ADD MODE in your form i..e u want to create new records

and this is why I posted what I said

Private Sub Form_Current()
    If Me.NewRecord = True Then
        Me.VehicleUID = Me.Parent.lstVehicles.Value
    End If
End Sub


this means when in insert a new record mode, set the vehicle id to be the selected one in the parent

0
 
Cyber-DrugsAuthor Commented:
LPurvis,

Isn't that what I am already doing here?  :
Me.VehicleUID = Me.Parent.lstVehicles.Value


rocki,

I'm possibly getting confused, but I now have this:

Private Sub Form_Current()
    If Me.NewRecord = False Then
        DoCmd.RunCommand acCmdRecordsGoToNew
    End If
    If Me.NewRecord = True Then
        Me!VehicleUID = Me.Parent.lstVehicles.Value
    End If
End Sub

and it's giving the same error, and highlighting the same line...

Maybe, just maybe, I have Friday syndrome...
0
 
Leigh PurvisDatabase DeveloperCommented:
No
Me.VehicleUID = Me.Parent.lstVehicles.Value
is requesting a value from the mainform be pulled into a subform value.
If the parent form hasn't loaded yet then it can't provide that value.

However if you did

Me.Subformname.Form.VehicleUID = Me.lstVehicles.Value

from the main form - then it has loaded when this code is called.
0
 
Cyber-DrugsAuthor Commented:
rocki,

I believe you are correct, as with a regular copy paste, it works perfectly! :)


LPurvis,

It seems it was my If statement rather than where my Form was looking for a Value. Cheers for the ideas though. :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.