update query from command button on form

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Hi all,

I have an update query that runs fine when I go to queries and select it but
when I reference it from the form that provides the criteria, it doesn't
update the record like it should. It should update the current record on
the form. How can I make that happen?

Mike
 
What does your Update query look like? What does it update, if anything?
When you say :"it doesn't update the record like it should", does this mean
it updates something or it updates nothing? Are you sure the criteria are
correctly referencing the controls on your form so that the WHERE clause of
the update is being built properly?
 
Hi all,

I have an update query that runs fine when I go to queries and select it but
when I reference it from the form that provides the criteria, it doesn't
update the record like it should. It should update the current record on
the form. How can I make that happen?

Mike
Click to expand...

By correcting the error in either your query or the code, which I
can't help with because you didn't post them; or by saving the record
on the form into the table prior to running the query.

Update queries don't update forms. They update TABLES; if the form has
a record open, it won't have been saved to disk, and even if it has
been saved, won't be available for the query since you have it open
for editing already. You may be asking the impossible!


John W. Vinson[MVP]
 
Here's my update query. It doesn't update at all. I have a button to open
it from the form named Loads

UPDATE DISTINCTROW Loads INNER JOIN Rates ON Loads.ID = Rates.ID SET
Rates.CitytoCityRate = DLookUp("[FlatRate] ","CustomerPtoP","[OriginCity]="
& [Forms]![Loads]![Rates]![OriginCity] & " And [DestinationCity]= " &
[Forms]![Loads]![Rates]![DestinationCity] & "And [CustomerID]= " &
[Forms]![Loads]![CustomerID]), Rates.Miles =
[Forms]![Loads]![MilestoConsignee], Rates.LinehaulRPM =
DLookUp("[RatePerMile]","CustomerZiptoZip","[CustomerID]=" &
[Forms]![Loads]![CustomerID] & "and [MinOriginZip] <=" &
[Forms]![Loads]![Combo43] & "and [MaxOriginZip] >=" &
[Forms]![Loads]![Combo43] & "and [MinDestinationZip] <=" &
[Forms]![Loads]![Combo45] & "and [MaxDestinationZip] >=" &
[Forms]![Loads]![Combo45]), Rates.ZIPtoZIPRate = [Miles]*[LinehaulRPM]
WHERE (((Loads.ID)=[Forms]![Loads]![ID]));


I am trying to save the value because we will change the rates from time to
time and I need to freeze them at the time of entry.

Thanks for replying,
Mike
 
Here's my update query. It doesn't update at all. I have a button to open
it from the form named Loads

UPDATE DISTINCTROW Loads INNER JOIN Rates ON Loads.ID = Rates.ID SET
Rates.CitytoCityRate = DLookUp("[FlatRate] ","CustomerPtoP","[OriginCity]="
& [Forms]![Loads]![Rates]![OriginCity] & " And [DestinationCity]= " &
[Forms]![Loads]![Rates]![DestinationCity] & "And [CustomerID]= " &
[Forms]![Loads]![CustomerID]), Rates.Miles =
[Forms]![Loads]![MilestoConsignee], Rates.LinehaulRPM =
DLookUp("[RatePerMile]","CustomerZiptoZip","[CustomerID]=" &
[Forms]![Loads]![CustomerID] & "and [MinOriginZip] <=" &
[Forms]![Loads]![Combo43] & "and [MaxOriginZip] >=" &
[Forms]![Loads]![Combo43] & "and [MinDestinationZip] <=" &
[Forms]![Loads]![Combo45] & "and [MaxDestinationZip] >=" &
[Forms]![Loads]![Combo45]), Rates.ZIPtoZIPRate = [Miles]*[LinehaulRPM]
WHERE (((Loads.ID)=[Forms]![Loads]![ID]));


I am trying to save the value because we will change the rates from time to
time and I need to freeze them at the time of entry.

Thanks for replying,
Mike
Click to expand...

Any Text fields must have their criteria delimited by either ' or "
delimiters. Which fields are numeric and which are text?

Also, you have missing blanks. For instance, if OriginCity is San
Francisco and DestinationCity is Boise, your DLookUp would contain the
string

[OriginCity] =San Leandroand [DestinationCity]= Boiseand

I would recommend a completely different approach, NOT using a DLookUp
at all! Use a Join query instead; could you explain the meaning of the
ID field? Why does Rates have an ID?

John W. Vinson[MVP]
 
John,

Believe me I am for a completely different approach!

I put an ID in rates to connect it to the loads table. Should I make them
into one table?

Any advice is really welcome!

Mike


John Vinson said:
Here's my update query. It doesn't update at all. I have a button to open
it from the form named Loads

UPDATE DISTINCTROW Loads INNER JOIN Rates ON Loads.ID = Rates.ID SET
Rates.CitytoCityRate = DLookUp("[FlatRate] ","CustomerPtoP","[OriginCity]="
& [Forms]![Loads]![Rates]![OriginCity] & " And [DestinationCity]= " &
[Forms]![Loads]![Rates]![DestinationCity] & "And [CustomerID]= " &
[Forms]![Loads]![CustomerID]), Rates.Miles =
[Forms]![Loads]![MilestoConsignee], Rates.LinehaulRPM =
DLookUp("[RatePerMile]","CustomerZiptoZip","[CustomerID]=" &
[Forms]![Loads]![CustomerID] & "and [MinOriginZip] <=" &
[Forms]![Loads]![Combo43] & "and [MaxOriginZip] >=" &
[Forms]![Loads]![Combo43] & "and [MinDestinationZip] <=" &
[Forms]![Loads]![Combo45] & "and [MaxDestinationZip] >=" &
[Forms]![Loads]![Combo45]), Rates.ZIPtoZIPRate = [Miles]*[LinehaulRPM]
WHERE (((Loads.ID)=[Forms]![Loads]![ID]));


I am trying to save the value because we will change the rates from time to
time and I need to freeze them at the time of entry.

Thanks for replying,
Mike
Click to expand...

Any Text fields must have their criteria delimited by either ' or "
delimiters. Which fields are numeric and which are text?

Also, you have missing blanks. For instance, if OriginCity is San
Francisco and DestinationCity is Boise, your DLookUp would contain the
string

[OriginCity] =San Leandroand [DestinationCity]= Boiseand

I would recommend a completely different approach, NOT using a DLookUp
at all! Use a Join query instead; could you explain the meaning of the
ID field? Why does Rates have an ID?

John W. Vinson[MVP]
Click to expand...
 
John,

Believe me I am for a completely different approach!

I put an ID in rates to connect it to the loads table. Should I make them
into one table?
Click to expand...

Since I have NO trace of an idea what the tables represent in the real
world, I have no idea.

Each table should represent an "Entity" - a real-life person, thing,
or event. Just from table names (which is all that I have to go on) a
Load is one type of entity, and a Rate is a different type of entity;
I really don't know what information you need to know about a Load or
about a Rate.

However, I do know that you should NOT have the same fields duplicated
in the two tables. If you need to apply a selected rate to a load, you
would need ONLY a foreign key - RateID - to the Rates table stored in
the Loads table. How you update it and enter it depends on your table
structure and your business model, which - again - you know much
better than I.

John W. Vinson[MVP]
 
Thanks John, I really appreciate the help. I am going to merge the two
tables since they are one-to-one anyway.

I am also going to remove any fields from the forms that are calculated. I
am hoping this allows my update query to properly function.

Thanks again,
Mike
 
Back
Top