help with some table/query logic .. please?

[zahara666]

I have a query that pulls up (lets say for simplicity sake), a client code, file number, last name, first name and invoice number...

CC, FN, LN, FN, IN

1 1 Smith, John, E123
1 2 Doe, Jane, E123
2 5 Renolds, Ron, E124
2 6 Black, Joe, E124
3 10 Tang, Jack, E125

So the query looks like that.. The problem is that I want to assign the first invoice number in the query (E123) and I need it to be the same for the same Client Code and then autotmatically increase by 1 for the next client code and so on...

Hope this makes sense.. Thanks in advance..

Julie

 

[hilbertl]

Are you trying to populate the invoice field in the table that underlies the query with numbers that increase sequentially?

 

[zahara666]

Yes.. I put in those numbers E123, E124 and E125 for illustrative purposes.. But I want to be able to populate the query and thus the table with E123, E124 and E125. Starting with physically inputting the value E123 and letting it do the rest automatically.

Hope that makes sense?

Julie

 

[hilbertl]

Do you not have access to the table... why don't you update it directly? In any case, you will need to use VB to do this kind of updating for sure.
I had to build a module last month that does pretty much what you need, but its quite lengthy. If you care to send me send me your email address I can forward it you. Basically you will need to declare the table/select query as a recordset and use a For...Next loop to do the updates.
An input box can start the process by asking the user to supply to supply the start integer.
hlindo@ocwen.com.

 

[hilbertl]

Sorry. The code wasn't as long as I thought. And I used a Do...until Loop not a For...Next. I just tested it so you should be fine. Let me know if you have any issues.

Dim dbs As Database
Set dbs = CurrentDb
Dim nwtbls As Recordset
Dim ctr As Double
Dim strInput As String
Dim start As Double
Set nwtbls = dbs.OpenRecordset("century&quot 'Enter your query in quotes here.
'Make sure your records are sorted in the order you want

start = InputBox(Prompt:="Please enter start number", Title:="Access", XPos:=2000, YPos:=2000)

'AutoNumber Loop
Let ctr = 1
nwtbls.MoveFirst
nwtbls.Edit
nwtbls!temp = "E" & start 'Replace temp with the name of the field you're updating
nwtbls.Update
nwtbls.MoveNext

Do Until ctr = nwtbls.RecordCount
Let ctr = ctr + 1
Let start = start + 1
nwtbls.Edit
nwtbls!temp = "E" & start
nwtbls.Update
nwtbls.MoveNext
Loop

Set nwtbls = Nothing

End Sub

 

[zahara666]

I'm a little confused... Firstly how do you reference a specific column in the query? Your query is called century...?

secondly, i don't think this code does a check, like i need, to increment only when the client code changes?

i am not very familar with recordsets and how they work.. thanks very much for all your help,

Julie

 

[BoxHead]

What if Invoice number was simply a string built with the letter "E" & 122 plus the Client Code value?

In QBE,

InvNum: "E" & 122 + [ClientCode]

HTH


John

Use what you have,
Learn what you can,
Create what you need.

 

[zahara666]

That still doesn't solve the problem of having to increment only when the client code changes... Because once you go the next record it would become "E" & 123 + [Clientcode].. The point is incrementing only when the client code changes..

J.

 

[BoxHead]

Why would the number 122 become 123 when you went to the next record? John

Use what you have,
Learn what you can,
Create what you need.

 

[zahara666]

Well, that is the point.. When you get to a new client code you need a different invoice number, hence the reason to increment it. I can't have all the same invoice numbers for different clients. If you look at the first message I have posted (on top) you will see the illustration I drew of what the query looks like.

Hopefully this will help??

I'm currently getting an error message that says: "Too few parameters. Expected 2" when I try to open a recordset in the following way:
Set rst = dbs.OpenRecordset("InvoiceQuery&quot

rst is a recordset and dbs is the currentdb

Any ideas??
Thanks for your help and patience..

J.

 

[BoxHead]

Julie,

I'm looking at this and thinking that the since the Client Code is incremented, adding it to a static number will yield an incremented result.

CC, FN, LN, FN, IN

 1   1   Smith,   John,  E123
 1   2   Doe,     Jane,  E123
 2   5   Renolds, Ron,   E124
 2   6   Black,   Joe,   E124
 3   10  Tang,    Jack,  E125

CC, IN (IN - CC)

 1    E123  (123-1 = 122)
 1    E123  (123-1 = 122)
 2    E124  (124-2 = 122)
 2    E124  (124-2 = 122)
 3    E125  (125-3 = 122)

If you could post the SQL from the query it could help.


John

Use what you have,
Learn what you can,
Create what you need.

 

[zahara666]

John - my bad! Sorry.. I see where you are going with your logic, but the client code doesn't always (or ever really) increment by one. I was just using it for an illustrative purpose to show the different client codes. I should have been more clear on that.. The client code could be 28043 and then 28345 or even 28345B.

As for the error message I am getting with the openrecordset command - there is no sql code associated with that.. Is that what you were refering to?

Thanks,
Julie

 

[BoxHead]

Julie,

If you go into the design view (QBE grid) of "InvoiceQuery" and click on the 'View' menu, you can select 'SQL View'. If you copy and paste that, it might help us see where the problem is.

When you refer to the saved query "InvoiceQuery" in your open recordset code, I believe the SQL is actually running just as if you had included it in the code. John

Use what you have,
Learn what you can,
Create what you need.

 

[tomschafer]

Why don't you remove the InvoiceNumber field from this table then create another table that has CustomerCode and InvoiceNumber. Then join that table into your existing query by CustomerCode????

 

[zahara666]

Here is the SQL code that is giving me grief on the openrecordset command:

SELECT Int1Table.InvoiceNumber, ClientTable.ClientName, ClientTable.ClientContact, ClientTable.Address1, ClientTable.Address2, ClientTable.City, ClientTable.Zip, Int1Table.FileNum, Int1Table.LastName, Int1Table.FirstName, Int1Table.LOS, Int1Table.Rate, Int1Table.Surcharge, Int1Table.Total, Int1Table.Invoiced, Int1Table.Received, Int1Table.ClientCode
FROM ClientTable INNER JOIN Int1Table ON ClientTable.ClientCode = Int1Table.ClientCode
WHERE (((Int1Table.Invoiced)=False) AND ((Int1Table.Received) Between [Forms]![InvoiceForm]![FromDate] And [Forms]![InvoiceForm]![ToDate]) AND ((Int1Table.ClientCode)='28000' Or ([Int1Table].[ClientCode])='28010' Or ([Int1Table].[ClientCode])='28012' Or ([Int1Table].[ClientCode])='28020')) ORDER BY Int1Table.ClientCode;

But the Int1Table.ClientCode = '#####' could go on for many more... There is just 4 to show you..

Thanks for your help!!
Julie