Update Query??

[lead 27]

Hi
I have a command button on my form which when clicked changes a date in a field of evey record in a table. I have updated the code so that an extra msgbox comes up asking if it was the current user who did teh work. If the user clicks yes a field changes to their login if they click no then it changes to unknown.

All this work but I want to be able to change evry record the same as i have with the date ( i have an update query for the date) but how can i do this if there are two possible outcomes?

Here is the code incase it help you know what i mean

Private Sub Command65_Click()
If MsgBox("Are you sure you want to change this?", vbQuestion + vbYesNo, "change Requested") = vbYes Then
DoCmd.RunMacro "macro3"
End If
If MsgBox("Did you carry out the 100% check?", vbQuestion + vbYesNo, "change requested") = vbYes Then
Me.user = [CurrentUser]
Else
Me.user = "unknown"
End If
End Sub

Macro 3 runs my update query for the date field

Thank you

 

[ajetrumpet]

I have a command button on my form which when clicked changes a date in a field of evey record in a table.

OK.

I have updated the code so that an extra msgbox comes up asking if it was the current user who did teh work. If the user clicks yes a field changes to their login if they click no then it changes to unknown.

Is this relevant??

All this work but I want to be able to change evry record the same as i have with the date ( i have an update query for the date) but how can i do this if there are two possible outcomes?

You lost me...be more specific about the PROBLEM. How can you do WHAT?? Change every record the same as the date??

 

[lead 27]

Thanks for replying. Yes this function is relevent as it acts as a trail if anything goes wrong which my bosses want.

My problem is that the user will carry out a physical 100% check of inventory items then click the button to say it has been completed and that he DB is correct. I have a field in the table to say who has carried out the check. I need every field to be updated at the same time which would normaly be an update query however I dont know what to do as there are 2 possible outcomes (user name or unknown). The date was just an example of how I have already done it with one outcome.

 

[ajetrumpet]

I need every field to be updated at the same time which would normaly be an update query however I dont know what to do as there are 2 possible outcomes (user name or unknown).

Explain more about the "fields being updated at the same time"...?? There will be different UPDATE VALUES based on the value of the USERNAME field?? I thought they were just checking inventory?? That needs clarification....

Initially, to answer your question, I was thinking of two nested IIF functions, but it doesn't sound like you even need to go that far. The first two functions I would look at using are the SWITCH and IIF functions. You only need to write either of those one time to satisfy an update that has two conditions...

 

[lead 27]

Hi
When a user carries out a check of the stock they can click a command button which changes the date the last check was done. I have now added a field called "user", this is populated by, when the user clicks the button it asks if he carried out the check (there is no guarentee that he did so I need an option that he didnt) if the user clicks yes then the field changes to their login name if no it changes to unknown.

This all works fine for one record but my problem is that the details WILL be the same for each record in that table, so I need it so that once the button is clicked every record in the table is updated. I have done this with the date field by using an update query but i dont know how to do it with the user name field

I hope I have made it clearer

 

[lead 27]

Another option is instead of asking if the user carried out the check to just ask the user to enter a name.
Would this make things easier and if so how could I do it?

 

[ajetrumpet]

Ok Lead, I will give this one more try...

From what you're saying...??

the details WILL be the same for each record in that table, so I need it so that once the button is clicked every record in the table is updated.

it sounds like you have a table that keeps records that represent the CHECKS or INSPECTIONS that are run on your inventory...correct??

If that is true, and it is also true that you want to update EVERY value in the [username] field of that table to the value that resulted from the cmd click, then you shouldn't have a problem....is this getting closer to answering your question, or helping you figure out what you need to do??

 

[ajetrumpet]

Another option is instead of asking if the user carried out the check to just ask the user to enter a name.

I don't think it matters one way or the other really...

Can you post the file so I have a visual...or maybe a lookalike as an example?? Or even a screenshot of your form and table in question would be fine...

 

[lead 27]

Hi
Yes that is all correct. Sorry its taken some time

 

[ajetrumpet]

Sorry its taken some time

The problems that are solved effectively are usually the ones that take the most time...

So, you won't need to do anything really, but a simple update query, with an IIF function in it...

UPDATE [table] SET
field1 = IIF([user name box] = "unknown", "unknown", [user name box reference syntax]);

Or simply...

UPDATE
field1 = [username box reference syntax]

 

[lead 27]

Hi
Thank you for that but could you just simplify that code a little (just explaine the syntax parts i.e what is it?)
The table is 3 sqn
The field is user

Let me know if there is anything else you need

 

[ajetrumpet]

Lead,

This thread seems to be a continuation of THIS one. From what I read there, I'm not really convinced that you have a setup that is leading you down the right path. Can you not post your database so I can see if this is true?? If it's too sensitive, that's alright, but that might be the best thing to do.

Regarding the syntax...

UPDATE....
usernamefield = IIF([objectgroup]![objectname]![controlname] = "unknown", 
"unknown", [objectgroup]![objectname]![controlname])

I'm not even sure if this is correct for you though, because I'm not 100% sure I'm visualizing what you're describing...

 

[jcr587]

Combo Box Search

Dear Folks, Can anybody provide me a code on how to alternately search in two fields in a combo box by pressing maybe an assigned Key to trigger the switching search.(Example: search by ID_NO and by CUST_NAME). Thanks