Update Status field by record selection

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

accessuser - 04 Mar 2007 20:13 GMT

Table 1 Name: Bank
Table 2 Name: Ledger

1 Main form with 4 subforms (only having problem with 2 subforms)
1st Subform: UnMatch1 (query based on Ledger)
2nd Subform: UnMatch2 (query based on Bank)

Once my users verfied information from both subforms, they will decide if
those items match (by record) on the Status field.

I need to have a CmdMatch which can allow users to select particular record
and make update the Status to Match or UnMatch.

I did some research all day but only one that's close enough to what I wanted
is the following code:

Private Sub CmdMatch_Click()
Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb

mySQL = "UPDATE Ledger SET Status = ""Matched"" WHERE [Status] = '" & Status
& "'"

CurrentDb.Execute mySQL

End Sub

This code changes all records to "Matched" in the Status field. ??

Thanks in advance.

Reply to this Message

Mr B - 05 Mar 2007 00:09 GMT

You need one more critera in your sql statement. You are simply requesting to
happen exactly what you are describing that is happening.

You did not indicate what you have for a Key field in the "Ledgere" table.
What ever that key field is, you would need to add the value from that field
for the current record to the criteria. That will specify the one and only
record that is to be updated.

Signature

HTH

Mr B

> Table 1 Name: Bank
> Table 2 Name: Ledger
[quoted text clipped - 27 lines]
>
> Thanks in advance.

Reply to this Message

accessuser - 05 Mar 2007 01:23 GMT

The field that I want to update is "status" field. I have it in continuous
form right now, so I want it to change from UnMatched to "Matched" base on
the record I select. I only want it to apply to whatever record I select.
How do i do that??

Thanks.

>You need one more critera in your sql statement. You are simply requesting to
>happen exactly what you are describing that is happening.
[quoted text clipped - 8 lines]
>>
>> Thanks in advance.

Reply to this Message

Mr B - 05 Mar 2007 02:14 GMT

You must include in the "WHERE" part of your sql statement the value of the
Key field for the record you want to update.

You just add to the "WHERE" part of the sql statement. Something like:

mySQL = "UPDATE Ledger SET Status = ""Matched"" WHERE [Status] = '" & Status
& "' AND [Name of Key field from the Ledger table] = value of Key field for
record to be updated""

You'll have to provide the exact field name from the Ledger table and the
value of the key field for the record you want to update.

Signature

HTH

Mr B

> The field that I want to update is "status" field. I have it in continuous
> form right now, so I want it to change from UnMatched to "Matched" base on
[quoted text clipped - 15 lines]
> >>
> >> Thanks in advance.

Reply to this Message

accessuser - 05 Mar 2007 17:37 GMT

[Name of Key field from the Ledger table] = value of Key field

What do you mean? My key field is "Status" and I don't know which record i
will select. It various depends on which record users select. Can you
explain it in more detail, i didn't write the code above, i did some research
on this site and that's how i got it. I'm confused your code. Do i just
type "Status" again in your [Name of Key field from the Ledger table]???

Thanks for taking the time to reply my question.

>You must include in the "WHERE" part of your sql statement the value of the
>Key field for the record you want to update.
[quoted text clipped - 12 lines]
>> >>
>> >> Thanks in advance.

Reply to this Message

Mr B - 05 Mar 2007 18:14 GMT

I seriously doubt that your "Dey" field is Status. Status was the field that
you indicated that you wanted to update for a selected record.

The "[Name of Key field from the Ledger table]" is the name of a field that
holds a unique value for each of your records in the "Ledger" table.
Normally this would be an "AutoNumber" type field that is automatically
incremented each time a user adds a record to the table.

Let's see if we can start from the top. How does the user select the record
that is to have the "Status" updated. If they are selecting this record from
a list box or a combo box, then the record source (query) must include the
unique value that will identify a specific record. The bound column for the
list box or combo box needs to be set to be that unique field. You can
display any other field(s) you like but the bound field needs to be the
unique (key) field. This wan, when the user selects a reocrd, the value of
the list box will be the unique (key) value for the selected record.

You would then use that value in the "WHERE" part of your sql statement.

Try something like this in the "AfterUpdate" event of the listbox or combox
where users are selecting the record to be updated. (This assumes that the
listbox or combo box has the Key value as the bound column.

Dim lngRecID as long
lngRecID = Me.NameOfYourListbox
mySQL = "UPDATE ledger Set Status = ""Matched"" WHERE YourKeyFieldName = " &
lngRecId & ""

CurrentDb.Execute mySQL

Be careful about the word wrapping in the sql statement. For now, just keep
it all on the same line.

The code above will read the value of the bound value for the selected
record, assign that value to a variable and then use that value in the SQL
statement.

Signature

HTH

Mr B

> [Name of Key field from the Ledger table] = value of Key field
>
[quoted text clipped - 22 lines]
> >> >>
> >> >> Thanks in advance.

Reply to this Message

accessuser - 06 Mar 2007 01:52 GMT

I'm sorry, after all these, i am still having problem with this code. Ex) my
user compare the UnMatched record to their check register, and found out,
there is an adjustment. They will do whatever they have to do, and then
change the UnMatched to Match, because they made an adjustment.

But after reading your code and tried for many hours, it is still not working.
Could you explain to me one more time. Maybe i missed something from your
code, but i tried so many different way.

Private Sub CmdMatch_Click()
Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb()

mySQL = "UPDATE Ledger SET Status = ""Matched"" WHERE [Status] = '" & Status
& "' AND [Check_Num]"

CurrentDb.Execute mySQL
End Sub

Check_Num is not my primary key for this Ledger Table. sometimes there could
be two, because one is the cleared amount by the bank, and the other might be
adjustments. Manually, users will have to type delete UnMatched, and type
Matched, then its all set. Any easy way to just tell the cmd to change its
value???

I really appreciate your help on this.

>I seriously doubt that your "Dey" field is Status. Status was the field that
>you indicated that you wanted to update for a selected record.
[quoted text clipped - 38 lines]
>> >> >>
>> >> >> Thanks in advance.

Reply to this Message

Mr B - 06 Mar 2007 02:35 GMT

Ok, we'll take another shot at it.

First, i notice that you are declaring a recordset and and then using that
variable to open the recordset. If you are working in the current database,
then you do not need to do this. You can just create your sql statement and
then execute it.

You may need to send me a copy of your database so I can actually see what
you are doing and how your tables are designed.

If you want to send me a copy, then attach it to an email and send it to:
mrbpolknospamplease@suddenlink.net (just remove the no spam please)

I will be happy to look at it and see what we can do.

Signature

HTH

Mr B

> I'm sorry, after all these, i am still having problem with this code. Ex) my
> user compare the UnMatched record to their check register, and found out,
[quoted text clipped - 66 lines]
> >> >> >>
> >> >> >> Thanks in advance.

Reply to this Message

accessuser - 06 Mar 2007 04:00 GMT

I tried to send the database to you by email for 4 times, but all came back
failed. Is there anything that you see is missing on my code. I am running
this code on the VBA, right?

>Ok, we'll take another shot at it.
>
[quoted text clipped - 15 lines]
>> >> >> >>
>> >> >> >> Thanks in advance.

Reply to this Message

Mr B - 06 Mar 2007 13:27 GMT

Yes, the code is VBA. It is not clear with what event you are using the code.

There are several questions that need to be addressed to assist you. That
is why I suggested that you send me a copy of the database.

Did you remove the words "nospamplease" from the email addres I provided?
If you remove this phrase it will work: mrbpolknospamplease@suddenlink.net

Signature

HTH

Mr B

> I tried to send the database to you by email for 4 times, but all came back
> failed. Is there anything that you see is missing on my code. I am running
[quoted text clipped - 19 lines]
> >> >> >> >>
> >> >> >> >> Thanks in advance.

Reply to this Message