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 :)

[twinnyfo]

First, it seems very strange that you would be using the values of a Combo box, rather than the index. Using the index is a much more proper way of doing things, and I can only presume that since you are looking at the value of the third column that the first column is (or should be) the index. The engine behind access works much more efficiently when using indexes, which is why they exist.

Second, the statement

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

doesn't wuite make any sense to me.

Again, if you are using an index the value of the combo box should be the same as if you looked it up in the Towns table.

I am also confused as to why you are adding one to the "Type" value.

[vartaxe]

i came up with this but it keeps giving me 001

Private Sub Numéro_du_projet_Click()
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 Sub
 

[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
 

[nico5038]

The comboboxes can be used to select the project(s) and I advise you to read the "cascading comboboxes" article (http://bytes.com/topic/access/insigh...mbo-list-boxes)

The only problem left is assigning a new project for a Localité / type combination.
I would use a separate [New] button to trigger such an event.
There you can find the MAX([Nurméro du projet]) WHERE Localité and Type are from the comboboxes. When no record is returned start with 1 otherwise add 1 to the found MAX.

Getting the idea ?

Nic;o)

PS: It's better to use no spaces in table and fieldnames. I would use NurméroDuProjet,thus making the brackets "[" and "]" obsolete in queries...

[vartaxe]

here is a sample db

if it helps

thanks

[nico5038]

Hi vartaxe,

I've posted in the past a sample that might come in handy:
http://bytes.com/attachments/attachm...tbeforenew.zip

First I would recommend to use this Object/Action principle as it's the same way as Access works with tables/queries/etc. You get to see all of them and (by the right-click) you can activate the action you want (Edit/Delete/etc.).

By making a selection form with a subform with all Projets, the user will easily navigate to the needed projet.
Next the buttons on the right show the actions like [New], [Update] [Print], etc.

Finally this sample shows the way I add a new record. This is by inserting it on forehand (the position where you can determine the sequencenumber of the new projet) and when the user cancels the operation, by deleting the inserted record.

Just press the [New] button and see the two combo's [CodePrefix] and [RecType]. After selecting them you get a code like you need for your projet. Here however the [CodePrefix] determines the number and in your case both combo's are needed.
So for determing the sequence number you can use a count of the number of localité and type and add +1. (think of the NULL when it's a new combination, use the NZ() function!)

In this sample datadase the +1 is handled in the module "modEPMroutines"

Lot's of info in this sample, but hope you're able to extract some usefull guidelines for your database from.

Getting the idea ?

Nic;o)

[vartaxe]

can't get it to work

Private Sub Numéro_du_projet_Click()
If Len(Me.Numéro_du_projet & "") < 1 Then
ElseIf Me.Localité.Column(2) = "" Or Me.Type.Column(2) = "" Then
MsgBox "Veuillez renseigner la localité et le type de projet."
Else
tSeq = Nz(DMax("Séquence", "Projets", "Localité=" & Me.Localité & " and " & "Type=" & Me.Type))
hiCount = Nz(DLookup("Compteur", "Projets", "Séquence=" & tSeq), 0) + 1
Me.Numéro_du_projet = Me.Localité.Column(2) & "-" & Me.Type.Column(2) & "-" & Format(Compteur, "000")
End If
End Sub
 

[vartaxe]

it is making me go nuts

Private Sub Numéro_du_projet_Click()
If Len(Me.Numéro_du_projet & "") < 1 Then
     If Me.Localité.Column(2) = "" Or Me.Type.Column(2) = "" Then
          MsgBox "Veuillez renseigner la localité et le type de projet."
          Exit Sub
     End If
     tSeq = Nz(DMax("Séquence", "Projets", "Localité='" & Me.Localité.Column(2) & "' and " & "[Type]='" & Me.Type.Column(2) & "'"), 0)
     hiCount = Nz(DLookup("Compteur", "Projets", "Compteur=" & tSeq & ""), 0) + 1
     Me.Numéro_du_projet = Me.Localité.Column(2) & "-" & Me.Type.Column(2) & "-" & Format(hiCount, "000")
End If
End Sub
 

[jforbes]

So Vartaxe, what is making you nuts?

What do you want to happen and what is actually happening?