UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Field Not Recognized In Vba Code, Access 2016    
 
   
mihail.mate
post May 28 2020, 05:36 PM
Post#1



Posts: 13
Joined: 15-May 20



Hi,
I have a form containing a field called [beneficiar]. And I have a query containing a field called [beneficiar] and another field called [deconfirmat]. I want to create an afterupdate event procedure that will take the value of [beneficiar] from my form and give me the value of the field [deconfirmat] from that record where [beneficiar] from my query equals [beneficiar] from my form. For some reason that I don't understand, the field [deconfirmat] from my query is not recognized. I tried the findfirst method, then I thought I'd just verify each record for the condition, but I get the error from the picture attached. The code breaks at the line no=[deconfirmat]. It was the same when using the findfirst method, and I just don't understand why it wouldn't work. Can anyone help me?


Private Sub Beneficiar_AfterUpdate()
Dim sol_qry As Recordset
Set sol_qry = CurrentDb.OpenRecordset("DeConfirmat_per_ben")
Dim ben As String
Dim no As Integer
Dim rasp As String


ben = [Beneficiar]

With sol_qry
.MoveFirst
Do Until .EOF
If [Beneficiar] = ben Then
no = [deconfirmat]
.MoveNext
Else
.MoveNext
End If
Loop
End With
If no <> 0 Then
rasp = InputBox("Aveti de confirmat " & no & " solicitari inchise de catre SUS. Confirmati acum? (d/n)", "Solicitari de confirmat")
If rasp = "d" Then
DoCmd.OpenForm "viz_sol_de inchis", acFormDS, , [Beneficiar] = ben
Else
GoTo 10
End If
End If
10: End Sub
Attached File(s)
Attached File  err.png ( 18.83K )Number of downloads: 0
 
Go to the top of the page
 
theDBguy
post May 28 2020, 05:54 PM
Post#2


UA Moderator
Posts: 78,470
Joined: 19-June 07
From: SunnySandyEggo


Hi. Welcome to UtterAccess!
welcome2UA.gif

Are you able to post a sample copy of your db?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
RJD
post May 28 2020, 08:31 PM
Post#3


UtterAccess VIP
Posts: 10,569
Joined: 25-October 10
From: Gulf South USA


Hi: First I would add DAO. to the Dim for the Recordset ...

Dim sol_qry As DAO.Recordset

Then, just to make sure, I would add Me! ...

ben = Me![Beneficiar]

But mostly I would rewrite the WHERE section of the OpenForm as follows ... (since [Beneficiar] and [ben] are strings and also need the quotes) ...

DoCmd.OpenForm "viz_sol_de inchis", acFormDS, , "[Beneficiar] ='" & ben & "'"

If this does not work, then, yes, post the db as theDBguy requested ...

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
projecttoday
post May 28 2020, 08:54 PM
Post#4


UtterAccess VIP
Posts: 12,381
Joined: 10-February 04
From: South Charleston, WV


No is a reserved word so don't call variable names no.

Dim num as Integer
Do Until .EOF
If [Beneficiar] = ben Then
num = [deconfirmat]
End If
.MoveNext
Loop

You only need 1 .MoveNext per loop.

--------------------
Robert Crouser
Go to the top of the page
 
mihail.mate
post May 29 2020, 01:27 AM
Post#5



Posts: 13
Joined: 15-May 20



Hi, everyone,

Thank you very much for the suggestions! I tried them all, but without success - I get the same message.

Here is the database attached. The form where I tried to create the afterupdate event procedure is called solicitari_benef and the event should happen after updating the field "Beneficiar".

Thank you again!
Mihail
This post has been edited by mihail.mate: May 29 2020, 01:34 AM
Attached File(s)
Attached File  mydb.accdb.zip ( 1.12MB )Number of downloads: 2
 
Go to the top of the page
 
projecttoday
post May 29 2020, 02:24 AM
Post#6


UtterAccess VIP
Posts: 12,381
Joined: 10-February 04
From: South Charleston, WV


When would [beneficiar] ever not be equal to ben?
There is no field called [deconfirmat].

--------------------
Robert Crouser
Go to the top of the page
 
mihail.mate
post May 29 2020, 03:28 AM
Post#7



Posts: 13
Joined: 15-May 20



I need [beneficiar] from the form "solicitari_benef" to be compared against [beneficiar] from the only query in the database; for that specific record where the two values are equal, I need to retrieve the value of the field [deconfirmat] which actually exists in that query.
Go to the top of the page
 
projecttoday
post May 29 2020, 03:42 AM
Post#8


UtterAccess VIP
Posts: 12,381
Joined: 10-February 04
From: South Charleston, WV


ben = [Beneficiar]
If [Beneficiar] = ben Then

This will always be true unless you change [beneficiar] or ben somewhere.
deconfirmat is not in table._solicitari.

--------------------
Robert Crouser
Go to the top of the page
 
mihail.mate
post May 29 2020, 03:58 AM
Post#9



Posts: 13
Joined: 15-May 20



My thinking was that when saying ben=[beneficiar] I was in the form. When saying If [beneficiar]=ben Then I was already in my query DeConfirmat_per_ben because above the If line I go into with/end with. After your post I tried putting the full name like forms![solicitari_benef]![beneficiar] and [deconfirmat_per_ben]![beneficiar], but the code behaved in the same way. The field [deconfirmat] doesn't exist in the table _solicitari indeed, but I am not using that table in this routine, I am using the query "deconfirmat_per_ben", and in this query, [deconfirmat] is the second field. Do query fields not work the same way as table fields do? I am not very experienced in VBA.

I now changed my field name from the query "DeConfirmat_per_ben" from Beneficiar into Benef. When running this code:
With sol_qry
.MoveFirst
Do Until .EOF
If [Benef] = ben Then
num = [deconfirmat]
.MoveNext
Else
.MoveNext
End If
Loop
I get the the exact same error message that I posted in the first post of this topic. If I use Me![Benef] instead, I get the error that Access can't find the field 'Benef' referred to in your expression. So the problem is using the With/End With block with the query "DeConfirmat_per_ben", but I checked the definition and it seems fine:
Dim sol_qry As dao.Recordset
Set sol_qry = CurrentDb.OpenRecordset("DeConfirmat_per_ben")
The field names of this query are now [benef] and [deconfirmat], but there seems to be something wrong with me telling the database to use this query.
This post has been edited by mihail.mate: May 29 2020, 04:39 AM
Go to the top of the page
 
projecttoday
post May 29 2020, 04:33 AM
Post#10


UtterAccess VIP
Posts: 12,381
Joined: 10-February 04
From: South Charleston, WV


If that field is part of the recordset query and within the with block then you must use a period:

.deconfirmat

--------------------
Robert Crouser
Go to the top of the page
 
mihail.mate
post May 29 2020, 04:43 AM
Post#11



Posts: 13
Joined: 15-May 20



I did change like this within the With block:
If .Benef = ben Then
num = .deconfirmat
It breaks at "If .benef=ben Then" and I get a compile error: method or data member not found
Go to the top of the page
 
projecttoday
post May 29 2020, 04:53 AM
Post#12


UtterAccess VIP
Posts: 12,381
Joined: 10-February 04
From: South Charleston, WV


DeConfirmat_per_ben has 2 fields: Beneficiar and DeConfirmat. benef is not one of them.

--------------------
Robert Crouser
Go to the top of the page
 
mihail.mate
post May 29 2020, 04:59 AM
Post#13



Posts: 13
Joined: 15-May 20



True, this is how the posted database was. I changed beneficiar into benef in the query after one of your posts in order to check that the with block works and it doesn't take the field with the same name from the query
Go to the top of the page
 
projecttoday
post May 29 2020, 05:37 AM
Post#14


UtterAccess VIP
Posts: 12,381
Joined: 10-February 04
From: South Charleston, WV


CODE
Private Sub cboBeneficiar_AfterUpdate()
Dim sol_qry As dao.Recordset
Set sol_qry = CurrentDb.OpenRecordset("DeConfirmat_per_ben")
Dim ben As String
Dim num As Integer
Dim rasp As String


ben = Me.cboBeneficiar

'With sol_qry
sol_qry.MoveFirst
Do Until sol_qry.EOF
If sol_qry("Beneficiar") = ben Then
num = sol_qry("deconfirmat")
'.MoveNext
'Else
'.MoveNext
End If
sol_qry.MoveNext
Loop
'End With
If num <> 0 Then
    rasp = InputBox("Aveti de confirmat " & no & " solicitari inchise de catre SUS. Confirmati acum? (d/n)", "Solicitari de confirmat")
        If rasp = "d" Then
        DoCmd.OpenForm "viz_sol_de inchis", acFormDS, "[Beneficiar] ='" & ben & "'"
        Else
        GoTo 10
        End If
    End If
10 End Sub

I changed benef back to beneficiar. I changed the name of the combobox on the form from Beneficiar to cboBeneficiar. I'm not sure if it works but there's no error.

--------------------
Robert Crouser
Go to the top of the page
 
mihail.mate
post May 29 2020, 10:16 AM
Post#15



Posts: 13
Joined: 15-May 20



Thank you so much! It works! I am very confused with the various syntax options. Thanks again!
Go to the top of the page
 
projecttoday
post May 29 2020, 11:27 AM
Post#16


UtterAccess VIP
Posts: 12,381
Joined: 10-February 04
From: South Charleston, WV


You're welcome.

--------------------
Robert Crouser
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    8th July 2020 - 01:30 AM