Variable uses an Automation type not supported in Visual Basic (1 Viewer)

murray83 · Jul 21, 2017

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

Code:

'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 · Jul 21, 2017

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 · Jul 21, 2017

this is the highlighted section ( see attached )

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

isladogs · Jul 21, 2017

Try

Code:

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

or

Code:

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

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

murray83 · Jul 21, 2017

ridders said:
Try

Code:

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

or

Code:

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 · Jul 21, 2017

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)

Code:

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 · Jul 24, 2017

ridders said:
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)

Code:

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

Variable uses an Automation type not supported in Visual Basic (1 Viewer)

murray83

Games Collector

Attachments

CJ_London

Super Moderator

murray83

Games Collector

Attachments

isladogs

MVP / VIP

murray83

Games Collector

isladogs

MVP / VIP

murray83

Games Collector

Similar threads

Users who are viewing this thread