ADO Code help

[tani1978]

Hello Friends,
I am using the following code in my access database but due to some reasons I am getting error Nr 2450 in this function. Please help me to figure out the problem.

Function such_bauteilverwendung()
  
  On Error GoTo err_zngnraftup
  
  Forms![Bauteileverwendung suchen].Form![Uform1].Form.RecordSource = "Bauteileverwendung"
  If Forms![Bauteileverwendung suchen].Form![Uform1].Form.ADODB.RecordsetClone.RecordCount = 0 Then
    MsgBox "Bauteil hat keine Verwendung !"
    Forms![Bauteileverwendung suchen].Form![ansehen].Enabled = False
    Forms![Bauteileverwendung suchen].Form![bearbeiten].Enabled = False
    Forms![Bauteileverwendung suchen].Form![Druckbild].Enabled = False
    Forms![Bauteileverwendung suchen].Form![drucken].Enabled = False
    SendKeys "%Z"
  Else
    Forms![Bauteileverwendung suchen].Form![ansehen].Enabled = True
    Forms![Bauteileverwendung suchen].Form![bearbeiten].Enabled = True
    Forms![Bauteileverwendung suchen].Form![Druckbild].Enabled = True
    Forms![Bauteileverwendung suchen].Form![drucken].Enabled = True
    SendKeys "%Z"
  End If

exit_zngnr_aftup:
  Exit Function

err_zngnraftup:
  Select Case Err
    Case Else
      MsgBox "Fehler " & Err & " in Funktion SUCH_BAUTEILVERWENDUNG"
  End Select
  Exit Function

End Function

 

[MajP]

I think that you are trying to reference a subforms recordsource here, but this does not look right to me. It should be the form collection, the form's name, the sub form control, and the subform control's form. You have:

Forms![Bauteileverwendung suchen].Form![Uform1].Form.RecordSource = "Bauteileverwendung"

I think you want

Forms![Bauteileverwendung suchen].Controls![Uform1].Form.RecordSource = "Bauteileverwendung"

Because you want the sub form control "Uform1" then the form object of the subform control

Now if you are have an Access project the recordset property returns a ADODB recordset, if you are using just Access then it returns a DAO recordset. I am not sure about this

If Forms![Bauteileverwendung suchen].Form![Uform1].Form.ADODB.RecordsetClone.RecordCount

how about just

If Forms![Bauteileverwendung suchen].controls![Uform1].Form.RecordsetClone.RecordCount

I know that ADODB recordsets (depending on lock type, cursors, and joins) do not always work with the recordcount property. In this case I think your fine. You may want to print out the recordcount to ensure that you are returning the recordcount. Some times you have to move last before getting the right count.

 

[PHV]

I don't think that a Form object has an ADODB property ...
You may try something like this instead:
If Forms![Bauteileverwendung suchen]![Uform1].Form.Recordset.RecordCount <= 0 Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MajP]

Also you can take a lot of advantage of the
With
code
end with
construct
This is not only shorter, but also speeds up your code.
Something like:

with Forms![Bauteileverwendung suchen]
 .controls![Uform1].Form.RecordSource = "Bauteileverwendung"
  If .controls![Uform1].Form.RecordsetClone.RecordCount = 0 Then
    MsgBox "Bauteil hat keine Verwendung !"
    .ansehen.Enabled = False
    .bearbeiten.Enabled = False
    .Druckbild.Enabled = False
    .drucken.Enabled = False
    SendKeys "%Z"
  Else
    .ansehen.Enabled = True
    .bearbeiten.Enabled = True
    .Druckbild.Enabled = True
    .drucken.Enabled = True
    SendKeys "%Z"
  End If
end with

I am assuming that in your code your refereces like

Forms![Bauteileverwendung suchen].Form![Druckbild]

"Druckbild" is a control on the main form.

 

[MajP]

Could you use a dcount?

if DCOUNT("fieldName",""Bauteileverwendung") = 0 then

 

[MajP]

or even shorter

dim blnHasRecords as boolean
blnHasRecords = True
with Forms![Bauteileverwendung suchen]
 .controls![Uform1].Form.RecordSource = "Bauteileverwendung"
   if DCOUNT("fieldName",""Bauteileverwendung") = 0 then
      blnHasRecords = false
     msgbox "Bauteil hat keine Verwendung !"
   end if
    .ansehen.Enabled = blnHasRecords
    .bearbeiten.Enabled = blnHasRecords
    .Druckbild.Enabled = blnHasRecords
    .drucken.Enabled = blnHasRecords
    SendKeys "%Z"
end with