update query conditional data

  • Thread starter Thread starter cporter
  • Start date Start date
C

cporter

I have the query below opening up in a macro to try and update the hour
meter records on certain equipment. I'm wanting the thing to ask for
the EFLH only when the MachID on the form = one of three possibilties.
The dialog box for EFLH keeps coming up regardless of the MachID, can
anyone see anything obvious that I'm doing wrong?


UPDATE FPM INNER JOIN tblFPMTemp ON FPM.MACHID = tblFPMTemp.MACHID SET
tblFPMTemp.EFLH = IIf(Forms!formFPMByNum!MACHID="chi-002" Or
Forms!formFPMByNum!MACHID="car-001" Or
Forms!formFPMByNum!MACHID="car-002",[enter elfh],0), FPM.EFLH =
IIf(Forms!formFPMByNum!MACHID="chi-002" Or
Forms!formFPMByNum!MACHID="car-001" Or
Forms!formFPMByNum!MACHID="car-002",[enter elfh],0)
WHERE (((tblFPMTemp.MACHID)=[Forms]![formFPMByNum]![MACHID]));
 
Queries are different from procedural code. A query will always prompt for a
all parameters.
You best bet might be to remove the prompt from the query and make it a
textbox on your form. You'd then have to write a little code to handle the
different conditions.

Barry
 
Thanks Barry,
There is a text box on the form. The query works better than my coding
attempts. DO you know of any resources that might help me formulate the
proper code?

Carl
 
The query is always going to ask for the value of [Enter elfh]. I can see
no way around it as you've designed this query.

UPDATE FPM INNER JOIN tblFPMTemp
ON FPM.MACHID = tblFPMTemp.MACHID
SET tblFPMTemp.EFLH =
IIf(Forms!formFPMByNum!MACHID="chi-002" Or
Forms!formFPMByNum!MACHID="car-001" Or
Forms!formFPMByNum!MACHID="car-002",[enter elfh],0),
FPM.EFLH =IIf(Forms!formFPMByNum!MACHID="chi-002" Or
Forms!formFPMByNum!MACHID="car-001" Or
Forms!formFPMByNum!MACHID="car-002",[enter
elfh],0)
WHERE (((tblFPMTemp.MACHID)=[Forms]![formFPMByNum]![MACHID]));

Perhaps you can test the value of the control in your macro and then run one
of two queries.
 
What experience, if any, do you have with VBA? If you are just getting
started, there are a number of good books to help.
If you don't want to absorb the learning curve for this, you might have to
find someone you could contract to do it for you. It shouldn't take an
experienced developer more than 1-2 hours.

Barry
 
I have very little experience with VBA. I'm an electrical engineer and
I was given this project mid January to convert a DBASE IV project
(500+ pages of code) into Access. Before this I knew nothing of either
program. I now have most of it working but there are small pieces like
this one that I have yet to figure out and now that the department is
testing it they want a few tweaks that the old program wouldn't do. My
predecessor aquired a few books like "Visual Basic 6 Database" and
"Mastering Access 97 Development" but nothing that really enlightens me
 
Is it always going to ask because it is a parameter in a query or
because I have the IIF statement worded wrong?

You suggest testing my control in a macro then running the query if
needed. What basic method would you suggest? I'm fairly ignorant of
how to write procedural codes.My thought was to open the form and, in
the "before update event", place code to check the machID control. If
the values where right I'd open a dialog box to enter in EFLH and place
a macro button there to run the update query, close the dialog box and
return to the form. HOW to do that is beyond my knowledge at the
moment.


John said:
The query is always going to ask for the value of [Enter elfh]. I can see
no way around it as you've designed this query.

UPDATE FPM INNER JOIN tblFPMTemp
ON FPM.MACHID = tblFPMTemp.MACHID
SET tblFPMTemp.EFLH =
IIf(Forms!formFPMByNum!MACHID="chi-002" Or
Forms!formFPMByNum!MACHID="car-001" Or
Forms!formFPMByNum!MACHID="car-002",[enter elfh],0),
FPM.EFLH =IIf(Forms!formFPMByNum!MACHID="chi-002" Or
Forms!formFPMByNum!MACHID="car-001" Or
Forms!formFPMByNum!MACHID="car-002",[enter
elfh],0)
WHERE (((tblFPMTemp.MACHID)=[Forms]![formFPMByNum]![MACHID]));

Perhaps you can test the value of the control in your macro and then run one
of two queries.


I have the query below opening up in a macro to try and update the hour
meter records on certain equipment. I'm wanting the thing to ask for
the EFLH only when the MachID on the form = one of three possibilties.
The dialog box for EFLH keeps coming up regardless of the MachID, can
anyone see anything obvious that I'm doing wrong?


UPDATE FPM INNER JOIN tblFPMTemp ON FPM.MACHID = tblFPMTemp.MACHID SET
tblFPMTemp.EFLH = IIf(Forms!formFPMByNum!MACHID="chi-002" Or
Forms!formFPMByNum!MACHID="car-001" Or
Forms!formFPMByNum!MACHID="car-002",[enter elfh],0), FPM.EFLH =
IIf(Forms!formFPMByNum!MACHID="chi-002" Or
Forms!formFPMByNum!MACHID="car-001" Or
Forms!formFPMByNum!MACHID="car-002",[enter elfh],0)
WHERE (((tblFPMTemp.MACHID)=[Forms]![formFPMByNum]![MACHID]));
Click to expand...
Click to expand...
 
Are you doing this in a macro? or are you doing this in VBA code module? I
would use a vba code module.

The query is always going to prompt for the parameter. It has nothing to do
with the structure of the IIF. I'm guessing that the query engine is
basically saying I have a parameter that I don't have a value for and I
(may) need the value. I won't proceed until I know what the value of this
parameter is.

I would probably use vba in the form. SInce I don't know when (which event
or from where ) you are trying to do this I can only post some very generic
UNTESTED AIRCODE as a suggestion. Note this code has no error handling, no
data validation in code, etc.

Dim vValue as Variant

If Forms!formFPMByNum!MACHID="chi-002" Or
Forms!formFPMByNum!MACHID="car-001" Or
Forms!formFPMByNum!MACHID="car-002" Then
'Get the value from a control on a form or ask the user for the value
vValue= Forms!formFPMByNum!SomeValueOnForm

'Or you can use
vValue= InputBox("What EFLH","EFLH",10)

ELSE
vValue= 0
END IF

If IsNumeric(vValue) = True Then
StrSQL = "UPDATE FPM INNER JOIN tblFPMTemp " & _
" ON FPM.MACHID = tblFPMTemp.MACHID " & _
" SET tblFPMTemp.EFLH =" & lngValue & _
" WHERE (((tblFPMTemp.MACHID)=""" & _
Forms]![formFPMByNum]![MACHID] & """"
CurrentDb().Execute StrSQL, dbFailOnError
Else
Msgbox "Invalid EFLH value"
End if

Is it always going to ask because it is a parameter in a query or
because I have the IIF statement worded wrong?

You suggest testing my control in a macro then running the query if
needed. What basic method would you suggest? I'm fairly ignorant of
how to write procedural codes.My thought was to open the form and, in
the "before update event", place code to check the machID control. If
the values where right I'd open a dialog box to enter in EFLH and place
a macro button there to run the update query, close the dialog box and
return to the form. HOW to do that is beyond my knowledge at the
moment.


John said:
The query is always going to ask for the value of [Enter elfh]. I can
see
no way around it as you've designed this query.

UPDATE FPM INNER JOIN tblFPMTemp
ON FPM.MACHID = tblFPMTemp.MACHID
SET tblFPMTemp.EFLH =
IIf(Forms!formFPMByNum!MACHID="chi-002" Or
Forms!formFPMByNum!MACHID="car-001" Or
Forms!formFPMByNum!MACHID="car-002",[enter elfh],0),
FPM.EFLH =IIf(Forms!formFPMByNum!MACHID="chi-002" Or
Forms!formFPMByNum!MACHID="car-001" Or
Forms!formFPMByNum!MACHID="car-002",[enter
elfh],0)
WHERE (((tblFPMTemp.MACHID)=[Forms]![formFPMByNum]![MACHID]));

Perhaps you can test the value of the control in your macro and then run
one
of two queries.


I have the query below opening up in a macro to try and update the hour
meter records on certain equipment. I'm wanting the thing to ask for
the EFLH only when the MachID on the form = one of three possibilties.
The dialog box for EFLH keeps coming up regardless of the MachID, can
anyone see anything obvious that I'm doing wrong?


UPDATE FPM INNER JOIN tblFPMTemp ON FPM.MACHID = tblFPMTemp.MACHID SET
tblFPMTemp.EFLH = IIf(Forms!formFPMByNum!MACHID="chi-002" Or
Forms!formFPMByNum!MACHID="car-001" Or
Forms!formFPMByNum!MACHID="car-002",[enter elfh],0), FPM.EFLH =
IIf(Forms!formFPMByNum!MACHID="chi-002" Or
Forms!formFPMByNum!MACHID="car-001" Or
Forms!formFPMByNum!MACHID="car-002",[enter elfh],0)
WHERE (((tblFPMTemp.MACHID)=[Forms]![formFPMByNum]![MACHID]));
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

syntax error 9
programming complex query/report 3
Lost on update query 2
Update Query Question 3
update query 2
Dynamic query sort of thing 4
Math problem in a function 3
Query Not Updating 1

Back
Top