By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,576 Members | 1,360 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,576 IT Pros & Developers. It's quick & easy.

access 2010 vba help

P: 6
hi,

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Project_Number_Click()
  2. If Me.Town.Column(2) = "" Or Me.Type.Column(2) = "" Then
  3. Me.Project_Number = ""
  4. Else
  5. Me.Project_Number = Me.Town.Column(2) & "-" & Me.Type.Column(2) & "-" & Me.Form.CurrentRecord
  6. End If
  7. 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 :)
Mar 30 '15 #1
Share this Question
Share on Google+
9 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,483
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.
Mar 30 '15 #2

P: 6
i came up with this but it keeps giving me 001

Expand|Select|Wrap|Line Numbers
  1. Private Sub Numéro_du_projet_Click()
  2. Compteur = Nz(DMax("Séquence", "Projets", "Localité=" & Me.Localité & " and " & "Type=" & Me.Type), 0) + 1
  3. Me.Numéro_du_projet = Me.Localité.Column(2) & "-" & Me.Type.Column(2) & "-" & Format(Compteur, "000")
  4. End Sub
  5.  
Apr 2 '15 #3

P: 6
this is what i have so far

Expand|Select|Wrap|Line Numbers
  1. Private Sub Numéro_du_projet_Click()
  2. If Me.Localité.Column(2) = "" Or Me.Type.Column(2) = "" Then
  3. Me.Numéro_du_projet = ""
  4. Else
  5. Compteur = Nz(DMax("Séquence", "Projets", "Localité=" & Me.Localité & " and " & "Type=" & Me.Type), 0) + 1
  6. Me.Numéro_du_projet = Me.Localité.Column(2) & "-" & Me.Type.Column(2) & "-" & Format(Compteur, "000")
  7. End If
  8. End Sub
  9.  



Apr 3 '15 #4

nico5038
Expert 2.5K+
P: 3,072
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...
Apr 3 '15 #5

P: 6
here is a sample db

if it helps

thanks
Attached Files
File Type: zip sample.zip (58.1 KB, 34 views)
Apr 7 '15 #6

nico5038
Expert 2.5K+
P: 3,072
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)
Apr 7 '15 #7

P: 6
can't get it to work

Expand|Select|Wrap|Line Numbers
  1. Private Sub Numéro_du_projet_Click()
  2. If Len(Me.Numéro_du_projet & "") < 1 Then
  3. ElseIf Me.Localité.Column(2) = "" Or Me.Type.Column(2) = "" Then
  4. MsgBox "Veuillez renseigner la localité et le type de projet."
  5. Else
  6. tSeq = Nz(DMax("Séquence", "Projets", "Localité=" & Me.Localité & " and " & "Type=" & Me.Type))
  7. hiCount = Nz(DLookup("Compteur", "Projets", "Séquence=" & tSeq), 0) + 1
  8. Me.Numéro_du_projet = Me.Localité.Column(2) & "-" & Me.Type.Column(2) & "-" & Format(Compteur, "000")
  9. End If
  10. End Sub
  11.  
Apr 8 '15 #8

P: 6
it is making me go nuts

Expand|Select|Wrap|Line Numbers
  1. Private Sub Numéro_du_projet_Click()
  2. If Len(Me.Numéro_du_projet & "") < 1 Then
  3.      If Me.Localité.Column(2) = "" Or Me.Type.Column(2) = "" Then
  4.           MsgBox "Veuillez renseigner la localité et le type de projet."
  5.           Exit Sub
  6.      End If
  7.      tSeq = Nz(DMax("Séquence", "Projets", "Localité='" & Me.Localité.Column(2) & "' and " & "[Type]='" & Me.Type.Column(2) & "'"), 0)
  8.      hiCount = Nz(DLookup("Compteur", "Projets", "Compteur=" & tSeq & ""), 0) + 1
  9.      Me.Numéro_du_projet = Me.Localité.Column(2) & "-" & Me.Type.Column(2) & "-" & Format(hiCount, "000")
  10. End If
  11. End Sub
  12.  
Apr 10 '15 #9

jforbes
Expert 100+
P: 1,107
So Vartaxe, what is making you nuts?

What do you want to happen and what is actually happening?
Apr 10 '15 #10

Post your reply

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