access 2010 vba help

[vartaxe]

hi,

i need help im new to access, vba, sql,... and need to adapt this:

Private Sub Project_Number_Click()
If Me.Town.Column(2) = "" Or Me.Type.Column(2) = "" Then
Me.Project_Number = ""
Else
Me.Project_Number = Me.Town.Column(2) & "-" & Me.Type.Column(2) & "-" & Me.Form.CurrentRecord
End If
End Sub

to actually check for which number to use for that type of project in that town.

so instead of "Me.Form.CurrentRecord"

it should check the "Towns" table where in the "Column(3)" line = "Me.Town.Column(2)" for column in that line = "Me.Type.Column(2)" and add +1

thanks for helping

 

[Cronk]

What are the row sources for Me.Town and me.type?

 

[vartaxe]

ME.TOWN actually points to towns table and me.type to types table

but i added types columns to the towns table all have 0 in it

 

[Cronk]

And can I guess what the table structures are?

Provide the SQL for the controls' row sources please.

What I mean is, go to the control properties and provide what is in the row source property.

 

[vartaxe]

towns

SELECT [Localités].[N°], [Localités].[Français], [Localités].[Abréviation], [Localités].[Commune], [Localités].[Réseau], [Localités].[A], [Localités]., [Localités].[F], [Localités].[L], [Localités].[M], [Localités].[R] FROM Localités ORDER BY [Français];

types

SELECT [Types].[N°], [Types].[Nom], [Types].[Abréviation] FROM Types ORDER BY [Nom];

ps: localités is towns

 

[vartaxe]

i added types columns in towns all of which have 0 in it

TOWN A B F L M R
SPAIN 0 2 5 0 3 0

if the new project is in SPAIN and type B is selected it should check that the latest one was SPAIN-B-2 so +1 the new one will be called SPAIN-B-3

 

[vartaxe]

im not sure how to proceed... ive seen people use sql in access vba or even dlookup function but dunno how to use it. :/

ps: and i don't think excel macros are the way to go

 

[Cronk]

Presumably you have a Projects table where you store the name of the project. What is the structure of this table.

Hopefully you have separate fields to hold the LocationID, the ProjectID and the sequential number.

If that were the case, you could use
DMax("SequenceNo", "tblProjects", "LocationID=" & me.Town.column(x) & " and " & "ProjectID=" & me.type.column)
to get the highest SequenceNo for the particular town and Type.

 

[vartaxe]

well there are 3 main tables

"Projects"
"Locations"
"Types"

the projects one is "clean" its mainly for input through the form which has one column for projectid called "Project_Number" then there is a cell linked to "locations" and another linked to "types"

locations has "location" "type_a" "type_b" "type_c" columns the types columns i added them because i though i could used those as counters

types has "type" column where lines have "type_a" "type_b" and so on

so if i understand correctly for this to work properly i should scrape the counters like what you proposed and use dmax?

something like this should work?

Private Sub Project_Number_Click()
Dim As
DMax("SequenceNo", "Projects", "LocationID=" & Me.Location.Column(2) & " and " & "TypeID=" & Me.Type.Column(2))
End Sub

it's driving me go nuts

 

[Cronk]

In your Projects table, it would be advisable to have a key field which is an autonumber called say ProjectID. This is a unique value for every record in the table. Other fields should be Location, Type and SequenceNo.

By concatenating the last 3 fields above you can get your Project name eg SPAIN-B-3

The Dmax code would look up the Projects table for the next SPAIN B project, find the maximum previous number is 3 and then give you 4 for the next Sequence number eg SPAIN-B-4

 

[vartaxe]

ok thats promising

so can you help me with the full vba code for this to work

actually my projects table already have those fields except for SequenceNo

hxxp://i.imgur.com/lt5SkqS.png?1

so i can add SequenceNo that's no problem but will i need to input data in sequenceno? because after the access file is ready i will have to import some data into it and it should work i will perhaps need to add sequenceno to the data i need to import?

 

[vartaxe]

understanding this isn't very easy because english is not my mother tong language

hxxp://xxx.techonthenet.com/access/functions/domain/dmax.php (dmax)
hxxp://allenbrowne.com/func-09.html (dmax)
hxxp://xxx.utteraccess.com/wiki/index.php/Sequential_Numbering (dmax)
hxxp://xxx.techonthenet.com/access/functions/string/concat.php (concat)
hxxp://allenbrowne.com/func-concat.html (concat)

 

[Cronk]

"so can you help me with the full vba code for this to work"

I get paid to write code for clients. I do not mind helping posters with specific problems with their code but I'm not going to spend time writing and debugging all of your code.

Somebody else might be more generous. Bon chance.

 

[jdraw]

Can you describe to readers in 4-5 lines WHAT you are trying to accomplish in simple English? You mention that English is not your native language, but it is quite good.
I think (guessing) that you may be constraining your options by jumping to HOW before we really understand WHAT you are trying to do.

Databases work very well if you keep 1 fact in 1 field. You can always concatenate fields if necessary for reporting etc.

Good luck.

 

[Solo712]

....
the projects one is "clean" its mainly for input through the form which has one column for projectid called "Project_Number" then there is a cell linked to "locations" and another linked to "types"
....

So the project table looks something like :

ProjectID
Sequence
LocationID
TypeID
TypeCounter

Is that correct ?

If that is correct you can retrieve the typecounter for that particular location and typeid by getting first the highest sequence number along the lines Cronk suggested but you will have to first look up both LocationId and Type ID as numbers so you would not use columns. Assuming column 0 is the binding columns in both cases then you could write :

tSeq = Nz(DMax("Sequence", "tblProjects", "LocationID=" & me!Town  & " and " & "TypeID=" & me!type))

If the DMax = 0 then you know that the counter for that type is 0, i.e. there was no project of this type before in this location. If there is a record in the highest sequence then you cn extract the type counter by :

hiCount = Nz(DLookup("TypeCounter", "tblProjects", "Sequence =" & tSeq))

Then you increase hiCount by one and create a new record for the new project for the Location and Type .... if that is what you want to do. If the idea for the Projects is just to have one record for each combination of Location and Type to keep the counter then the tSeq would be found by a DLookup function and you would just update the record by increasing hiCount.

Best,
Jiri

 

[vartaxe]

Private Sub Numéro_du_projet_Click()
Compteur = Nz(DMax("[Séquence]", "Projets", "Localité = " & Me.Localité.Column(2) & " and " & "Type = " & Me.Type.Column(2)), 0) + 1
Me.Numéro_du_projet = Me.Localité.Column(2) & "-" & Me.Type.Column(2) & "-" & Format(Compteur, "000")
End Sub

thanks for helping

i came up with this but doesn't work... keep getting error 2471

 

[vartaxe]

yeah got it

Private Sub Numéro_du_projet_Click()
Compteur = Nz(DLookup("Séquence", "Projets", "Localité=" & Me.Localité & " and " & "Type=" & Me.Type), 0) + 1
Me.Numéro_du_projet = Me.Localité.Column(2) & "-" & Me.Type.Column(2) & "-" & Format(Compteur, "000")
End Sub

 

[vartaxe]

oh sorry doesn't work very well because it keeps making 001
something missing :/

 

[vartaxe]

this is what i have so far

Private Sub Numéro_du_projet_Click()
If Me.Localité.Column(2) = "" Or Me.Type.Column(2) = "" Then
Me.Numéro_du_projet = ""
Else
Compteur = Nz(DMax("Séquence", "Projets", "Localité=" & Me.Localité & " and " & "Type=" & Me.Type), 0) + 1
Me.Numéro_du_projet = Me.Localité.Column(2) & "-" & Me.Type.Column(2) & "-" & Format(Compteur, "000")
End If
End Sub

 

[Solo712]

this is what i have so far

Private Sub Numéro_du_projet_Click()
If Me.Localité.Column(2) = "" Or Me.Type.Column(2) = "" Then
Me.Numéro_du_projet = ""
Else
Compteur = Nz(DMax("Séquence", "Projets", "Localité=" & Me.Localité & " and " & "Type=" & Me.Type), 0) + 1
Me.Numéro_du_projet = Me.Localité.Column(2) & "-" & Me.Type.Column(2) & "-" & Format(Compteur, "000")
End If
End Sub

Est-ce que ça marche ?

Jiri