Variable uses an Automation type not supported in Visual Basic

[murray83]

have the following code when the user presses to add a record

'adds record to main table
If Len(Me.txtDept1) >= 1 Then
DoCmd.RunSQL ("INSERT INTO main ( FromBay, ToBay, [Requested By], RequestID, Dept  )VALUES (fromBay.value, ToBay.value, txtUserLookup.value, txtusername.value, txtDept.value )")
'shows ballon tip for new record
ShowBalloonTooltip "New Move Request", "A New Request Has Been Placed", btInformation
Else
MsgBox ("Dept not popultaed, please wait")
End If

but when the dept ( as shown in error 0) says #Error i get the error message as shown in error 1

any help would be great

cheers

 

[CJ_London]

what happens when you click the debug button - what code is highlighted? This will tell you where or what the problem is

Also you seem to have a control called txtdept1 and your sql uses txtdept

And doesn't your sql need to reference those controls, and if they are text, use single quotes? e.g.

"....Values(" & me.frombay & ", "....", '" & txtusername & "', "......

 

[murray83]

this is the highlighted section ( see attached )

and the me.txtDept1 is looking at the lookup as i thought would populate faster

 

[isladogs]

Try

If Len(Nz(Me.txtDept1,""))>=1 Then

or

If Nz(Me.txtDept1,"")<>"" Then

However, if I were you I'd fix the #Error shown in txtDept1

 

[murray83]

Try

If Len(Nz(Me.txtDept1,""))>=1 Then

or

If Nz(Me.txtDept1,"")<>"" Then

However, if I were you I'd fix the #Error shown in txtDept1

how would you sugest i did fix the #Error

im guessing it says error as it cant done its dlookup as on load has no data ?

 

[isladogs]

how would you sugest i did fix the #Error

im guessing it says error as it cant done its dlookup as on load has no data ?

Why can't you use a bound control for the Dept textbox?
If it depends on the 'To Bay' combo box value, you could use code similar to that below
(changing all control names to those on your form)

Private Sub cboToBay_AfterUpdate()

Me.txtDept= ... enter DLookup code based on your table & combo box value
Me.txtDept.Requery

End Sub

And to avoid getting #Error when you load the form, either set a default value or
set Me.txtDept="" in the Form_Load event

 

[murray83]

Why can't you use a bound control for the Dept textbox?
If it depends on the 'To Bay' combo box value, you could use code similar to that below
(changing all control names to those on your form)

Private Sub cboToBay_AfterUpdate()

Me.txtDept= ... enter DLookup code based on your table & combo box value
Me.txtDept.Requery

End Sub

And to avoid getting #Error when you load the form, either set a default value or
set Me.txtDept="" in the Form_Load event

That works a bloody charm

many, many thanks