473,326 Members | 2,108 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

How do I identify one or more form records that have the same value in a given field

I generate an omnibus invoice for one or more completed taxi bookings, comprising details and costs taken from each selected job/booking record, but all sharing just the one Invoice Number (the next in the stack). This action ticks a temporary check-box in each chosen record. Once the invoice is dispatched I want to be able to search those records that are still checked and add the one invoice number (identified by DMax + 1) to the currently blank 'invoice number' field in each of them, as well as the date of dispatch Date() to the currently blank date field, all for future reference. I have been trying to do this with a macro. I open the Bookings table that feeds the form (via query) but I'm having probs identifying the checked records. Also not sure, if I crack that, how I can add the fresh data back into the relevant records.
Aug 20 '10 #1

✓ answered by NeoPa

Assuming you have the invoice number available in a long variable (lngInvNo) and also that you want to reset the selections on the selected records after updating them with the invoice number and date, then the following code (changing names where necessary) should create the SQL for you :

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2. Dim lngInvNo As Long
  3.  
  4. lngInvNo = Nz(DMax("[InvoiceNumber]", "YourTable"), 0) + 1
  5. strSQL = "UPDATE [YourTable] " & _
  6.          "SET    [Selected] = False " & _
  7.          "     , [InvoiceNumber] = " & lngInvNo & _
  8.          "     , [DispatchDate] = Date() " & _
  9.          "WHERE ([Selected])"
  10. Call CurrentDb.Execute(strSQL)

9 2201
liimra
119 100+
If you want to apply this using a macro, why don't you use the "setvalue" command. I mean to set the value you want in the field you want where the criteria you desire is met (two blank fields). Honestly, I don't know why some argue about this command but for me it is extremely useful.

Hope this helps if any,

Regards,
Ali
Aug 22 '10 #2
Thanks - SetValue seems to be just what I need, but when I test it I find that it acts on the first record but stops there. I need to pass through all records to see whether they are checked and add the invoice number accordingly. My macro design is as follows (the "5" and "10" are just testers to check the functionality at this stage).
Item: [Forms]![Bookings]![Inv No]
Expression: IIf([Forms]![Bookings]![Is Selected]=True,"5","10")
Aug 23 '10 #3
liimra
119 100+
Thanks Nick. Glad we are on the right track. Now, we need to use the loop function so we can achieve what you need.

The solution consists of three steps:

1) Create a Query based on the invoice table. Under criteria in Query Design View, Type "Is Null" without quotes. Note that in SQl view the statement is (replace names where necassary).

Expand|Select|Wrap|Line Numbers
  1. SELECT TableName.FieldName1, TableName.FieldName2
  2. FROM TableName
  3. WHERE (((TableName.FieldName1) Is Null));
  4.  
Of course you can modify it in the way you want depending on your needs. For example you can add more criteria (Invoice No, Date..etc).

2) Create a Multiple Item Form based on the just created Query.

3) Create a button and attach the following code to it:
Expand|Select|Wrap|Line Numbers
  1. Do
  2. FieldName = DMax("FieldName", "TableOrQuery") + 1
  3. Recordset.MoveNext
  4. Loop Until Recordset.EOF
  5.  
That is it, you are done. Furthermore, if you want not to get any error when there are no records, you can add If statement at the beginning like : If RecordCount = 0
Msg Box "No Record" Else..


Here is my favorite part and one thing I like about Access & VBA.

You can create a button that will do it for you (Open the form, update records, close back the record, give message of success or no result when there are no records). This will make your database looks extremely professional because all what the user would see is that button.

I have attached a database with the example.

The form with the button is the one displayed at opening. Once you click on the button "update records", All records will be updated. If the table (invoice table) contains no records without invoice number, you will get the message "no new records". If there are new records with no invoice number, the records will be updated with the required number (DMax + 1) and you will get the message "records updated".

The table has some missing invoice numbers; so first time you click "update records" the records will be updated. Next time it won't as all records will have invoice numbers.


It is simple and works great. I hope it will help you with this issue as well with future issues.

Regards,
Ali
Attached Files
File Type: zip Inv.zip (29.4 KB, 82 views)
Aug 24 '10 #4
Thanks for your very comprehensive reply and attachment Ali which I have been studying carefully. One problem is that mine is already quite a large and involved database and I would like to implement your proposals as closely to the current structure as possible. Also, this is a multiple invoice covering more than one job but all sharing the same 'next in the stack' Inv No. I have now created a query which displays all records (jobs) to be included on the next invoice, also a field (Next Inv No) which calculates the next Inv No using DMax on all those currently allocated. I have converted that query to a Multiple Item Form but the following code within it stops at the 3rd line giving "runtime error 2448 - You can't assign a value to this object":

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2. Do
  3. Forms![Invoice Query]![Inv No] = Forms![Invoice Query]![Next Inv No]
  4. Forms![Invoice Query].Recordset.MoveNext
  5. Loop Until Forms![Invoice Query].Recordset.EOF
  6. DoCmd.Close acForm, "[Invoice Query]"
  7. End Sub
Any ideas? Maybe it is my notation that is poor - I'm new to VBA.

Thanks
Nick
Aug 27 '10 #5
liimra
119 100+
You are welcome Nick. We are almost there. I don't have the database and the structure but I strongly believe that you are getting this error because you are placing the new field you created inside the form in a hidden state (May be in the head of the form and then you changed visibility of the head to "NO"). IF this is the case, just change the visibility to "Yes".

Even if you use the DMAX inside a field in the multiple items form, it will still gather the last entered value. Means (DMax + 1, DMax + 1 + 1, DMax + 1 +1 +1 and so on).
There is an easy way to overcome this. Use the "Setvalue" Function to set the value of the field in the multiple item form *on the load event*. Thus, you get a constant number.

I have copied more records into the table (14,400) and it is pretty fast I think. Kindly find what I just explained in the attached database. Three Variables are included: (1)The Form is now displayed as Hidden, so it won't be noticed.(2)The Invoice Number is now constant for all invoices. (3) More Records.

Your Code should be alright now (after placing the "Next Inv No" field in a visible area).

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load  'Use Form_Load
  2. Forms![Invoice Query]![Inv No] = Forms![Invoice Query]![Next Inv No]
  3. Forms![Invoice Query].Recordset.MoveNext
  4. Loop Until Forms![Invoice Query].Recordset.EOF
  5. DoCmd.Close acForm, "[Invoice Query]"
  6. End Sub
  7.  
Hint:It is better not to use space when naming objects. One simple advantage is that you won't need to enclose instances in brackets and your code would be:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load  'Use Form_Load
  2. Forms!InvoiceQuery!InvNo] = Forms!InvoiceQuery!NextInv No
  3. Forms!InvoiceQuery.Recordset.MoveNext
  4. Loop Until Forms!InvoiceQuery.Recordset.EOF
  5. DoCmd.Close acForm, "InvoiceQuery"
  6. End Sub
  7.  
Keep in mind that if "Next Inv No" is not set as fixed number, every record will be given the next DMAX number.


Best Regards,
Ali
Attached Files
File Type: zip Inv2.zip (34.4 KB, 92 views)
Aug 27 '10 #6
NeoPa
32,556 Expert Mod 16PB
Assuming you have the invoice number available in a long variable (lngInvNo) and also that you want to reset the selections on the selected records after updating them with the invoice number and date, then the following code (changing names where necessary) should create the SQL for you :

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2. Dim lngInvNo As Long
  3.  
  4. lngInvNo = Nz(DMax("[InvoiceNumber]", "YourTable"), 0) + 1
  5. strSQL = "UPDATE [YourTable] " & _
  6.          "SET    [Selected] = False " & _
  7.          "     , [InvoiceNumber] = " & lngInvNo & _
  8.          "     , [DispatchDate] = Date() " & _
  9.          "WHERE ([Selected])"
  10. Call CurrentDb.Execute(strSQL)
Aug 27 '10 #7
Thanks Liimra for all the assistance you have given. I have learnt much from your input, particularly about improving the user inteface, but the code from NeoPa worked off the shelf and could be incorporated directly into my existing database design with minimal modification. I'm very grateful to you both for your time.
Cheers
Nick
Aug 31 '10 #8
liimra
119 100+
You are welcome Nick. Happy it worked for you and glad I have added some value to this post and hope it will be helpful for others in future.

Please choose NeoPa's answer as "best answer" so this post appears as solved (more helpful for others).

Regards,
Ali
Sep 1 '10 #9
NeoPa
32,556 Expert Mod 16PB
I've been away, but I'm glad to return and find this all resolved nicely :) Good for you Nick.
Sep 6 '10 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: William Wisnieski | last post by:
Hello Again, I'm really stuck on this one.....so I'm going to try a different approach to this problem. I have a query by form that returns a record set in a datasheet. The user double...
4
by: Tom Keane | last post by:
Okay, woo, yet another issue I have. I remember writing about this issue AGES ago, but I don't think it worked, or I just left it for too long. I have a query that searches for specific records...
11
by: The Crow | last post by:
i have a arraylist. say it contains integer values. i want to be able to inform user, which indexes in the array contain same values. but there can be N different values, and M different indexes...
3
by: Brian Foree | last post by:
I am developing an ASP.NET application that uses Access 2000 as its backend, and have just started getting the following error on 2 ASP.NET pages that had been working until late last week (and I...
2
by: K B | last post by:
I bit new to this. My xml files contains the following nodes: <root> <WI Title="Test1"> <Role Name="Legal"/> <Role Name="PM"/> </WI> <WI Title="Test1"> <Role Name="Legal"/> <Role...
3
by: André | last post by:
Hi, I created a button in order to delete all the records at once in a gridview. But i get the error: No value given for one or more required parameters Thanks André The aspx file contains:...
2
mickyp
by: mickyp | last post by:
Hi There, I have an access database where I would like to set two fields to the same value. Both fields are in the same table called Sampletbl The one field is called Year and the other Years....
2
by: JC2710 | last post by:
Hi I would like a Query that Updates a field to indicate that records have a duplicate entry( records with same values). My table is ..... Code........Process..........Quantity ...
1
by: scottbouley | last post by:
I'm building a view in SQL Server 2000 Enterprize Manager to find any parent supply request records that are "Open" where all of the child detail records are "Completed". The two tables are related...
3
by: silverburgh.meryl | last post by:
Hi, Is there a STL algorithm to compare if 2 vector<inthave same values? Thank you.
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.