Solved

VBA - Run-time error '3314'

Posted on 2006-11-10
30
658 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
 
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now