Simpel SQL forespørgsel i en VB sub?

Private Sub kontonr_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If DCount("*", "<DB>", "[kontonr] = '" & Me!kontonr & "' AND Id <> " & Me!Id) > 0 Then
MsgBox "Kontonummer findes i forvejen!", vbCritical
DoCmd.OpenReport "RAPPORTNAVN", acViewPreview, , "[TABELNAVN]![ID] = " & Me!ID & ""
DoCmd.CancelEvent
Exit Sub
End If
End If
End Sub

"så jeg kan printe ID nummeret (nøglen)..." is a bit misleading. Do you want to print it to a report or just include it in the message box?

Private Sub kontonr_BeforeUpdate(Cancel As Integer)

  If Me.NewRecord Then
      If DCount("*", "<DB>", "[kontonr] = '" & Me!kontonr & "' AND Id <> " & Me!Id) > 0 Then

        MsgBox "Kontonummer findes i forvejen på ID: " & Me!Id , vbCritical

        Cancel = True

      End If
  End If
End Sub

Note also I have change the line
DoCmd.CancelEvent
to

Cancel = True

The before update event includes are parameter (cancel) setting this to true cancels the update.

Hi,

Yes it was a bit misleading sorry about that.

I really wanted the Id to be printed out in the message box. I tried your example Terry, but that just printes the 'current' Id for the form field. Meaning it will print the Id for the record I am currently trying to add and not for the record that already exists in the DB.

I believe we need to add Mugs example:
"[TABELNAVN]![ID] = " & Me!ID & ""

To get the correct Id number :)

You have an ID in your Dcount, if the ID isnt the correct on ethen your dcoundt doesnt work either.

I dont know anything about your tables etc but if ID is the primary key then your dcount can only ever return max 1.

Can you explain your tables in more detail?

"I believe we need to add Mugs example:"

The ID I use in the msgbox is the same mugs uses.

in the dcount you use <> Me!Id

If the ID is unique why do you need to include it in the Dcount?

I'm rather confused as to what purpose the ID has?

Hi Terry,

Frankly the code was something I got helpwith from eksperten.dk and for that I'm not sure what the Id is doing in the Dcount. I'm a noob when it comes to access and VB scripts.

However, I figured out that if I use the following instead:
DoCmd.OpenReport "Dublet", acViewPreview, , "[table]![kontonr] like " & Me!kontonr & ""

from Mugs example, I could find the correct row in the DB. The Id is simply an auto increment key in access, so Me!Id would give me the latest post obviously.

To drill the whole thing down, what I wanted was to be able to pull the ID from the already existing record in the DB and show it to the user. So I guess that would require a select * from table where kontonr = &Me!kontonr. That would give me 2 id's. The one already in the DB and the id of the current record in the form.

Mugs example works for me, but would still love to see how I could print the Id in the message box instead of a report.

Det har terry tidligere vist:

Private Sub kontonr_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If DCount("*", "<DB>", "[kontonr] = '" & Me!kontonr & "' AND Id <> " & Me!Id) > 0 Then
MsgBox "Kontonummer findes i forvejen på ID: " & Me!Id , vbCritical
DoCmd.CancelEvent
Exit Sub
End If
End If
End Sub

Jeg prøver igen :)

I tabellen er der en liste over tidligere indtastede konti:

ID    Kto
10    11111
11    22222
12    33333
13    44444
14    55555

Lad os sige en bruger er ved at indtaste i formularen og denne nye post har id 28. Brugeren indtaster konto 11111 og får en fejlmeddelelse. Her skulle der gerne stå "findes allerede på id 10".

Hvis jeg bruger:
MsgBox "Kontonummer findes i forvejen på ID: " & Me!Id , vbCritical

Vil den skrive "Findes allerede i forvejen på ID: 28".

Så i stedet for brugte jeg:
DoCmd.OpenReport "RAPPORTNAVN", acViewPreview, , "[TABELNAVN]![konto] = " & Me!konto & ""

Som giver mig 2 resultater:

ID    Kto
10    11111
28   

Der vil ikke være indsat noget for id 28, da VB scriptet ligger i en beforeupdate.

Så det jeg ville var bare at få messageboxen til at vise id nummeret 10 i ovenstående eksempel. Håber det giver bedre mening og beklager misforståelserne.

!
have you found a solution now?

Prøv nedenstående - Ikke afprøvet:

Private Sub kontonr_BeforeUpdate(Cancel As Integer)
dim intsearch as long
If Me.NewRecord Then
If DCount("*", "<DB>", "[kontonr] = '" & Me!kontonr & "' AND Id <> " & Me!Id) > 0 Then
intsearch = Me!konto
Msgbox "Det indtastede kontonr" & vbnewline & Me!konto & vbnewline & "findes allerede på ID: " & DLookup("[TABELNAVN]", "ID", "[konto]=" & intsearch)DoCmd.CancelEvent
Exit Sub
End If
End If
End Sub

Hej Mugs,

Det bliver mandag, skal nok melde tilbage :)

tak for jeres hjælp!