Solved

VBA - Run-time error '3314'

Posted on 2006-11-10
30
699 Views
Last Modified: 2010-05-18
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!
0
Comment
Question by:Cyber-Drugs
  • 12
  • 8
  • 6
  • +1
30 Comments
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 17912915
Hi there,

Is there a value in Me.Parent.lstVehicles.Value ?
0
 
LVL 4

Author Comment

by:Cyber-Drugs
ID: 17912928
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
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 17913008
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 17913020
also

this line looks a little suspect

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

Is lstVehicles a listbox control ?
0
 
LVL 4

Author Comment

by:Cyber-Drugs
ID: 17913208
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
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 17913222
What is it that you want to do - exactly?
0
 
LVL 4

Author Comment

by:Cyber-Drugs
ID: 17913228
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
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 17913303
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 17913306
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 17913331
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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17913347
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
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 17913378
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
 
LVL 4

Author Comment

by:Cyber-Drugs
ID: 17913423
Hi guys,

Sorry I got side-tracked for a few, I'm just going to read all recent replies now...
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17913435
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
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 17913437
how about Erdinger? smashing stuff !
0
 
LVL 4

Author Comment

by:Cyber-Drugs
ID: 17913472
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
 
LVL 4

Author Comment

by:Cyber-Drugs
ID: 17913479
OK, we can discuss beer after I've solved this problem, calm down boys. :P
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17913500
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
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 17913505
Oops - didnt catch that!

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

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



0
 
LVL 4

Author Comment

by:Cyber-Drugs
ID: 17913511
Sorry for my ignorance rocki, but "addmode" ? Can't say I've heard of that yet...
0
 
LVL 4

Author Comment

by:Cyber-Drugs
ID: 17913517
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
 
LVL 4

Author Comment

by:Cyber-Drugs
ID: 17913758
rocki,

Could you explain that "addmode"


everyone else,

Any other ideas?
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17913799
EE-Stuff it?  Possibly one of those things that just takes a jiffy - but helps if you're sat infront of it.
0
 
LVL 4

Author Comment

by:Cyber-Drugs
ID: 17913824
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17913878
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 17913887
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
 
LVL 4

Author Comment

by:Cyber-Drugs
ID: 17913968
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
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 17913985
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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17913986
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
 
LVL 4

Author Comment

by:Cyber-Drugs
ID: 17914074
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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question