Duplicating record and double table check

[tsuby]

Hello everybody,

I am stuck with an append query and I need some advice from expert.

I have a database with three tables.

Table_Customers (PK Autonumber)
TableB_Orders (PK Autonumber)
TableC_Products (PK Autonumber)

The three tables are linked through a common field (CustomerNumber).

Now, my problem: sometimes I need to duplicate an order from the
Orders_Form. The duplicated order is not always for the same customer,
so I need to

1) change the CustomerNumber accordingly (I setup an Espr1 field on the
query), but I
2) need to make sure that the CustomerNumber is already in
Table_Customers as well and, if it is not so, I
3) need to get an error message saying that the records could not be
copied.

1+3 I know how to do. Point 2 is beyond my knowledge.

I've been stuck there for days now and it's driving me crazy!

Any suggestions?

Thanks

Erika

 

[Steve Schapel]

Erika,

One possible approach...
If DCount("*","Table_Customers","CustomerNumber=" &
YourProposedNumber) = 0 Then
' your error message
Else
' go ahead
End if

 

[tsuby]

Steve Schapel ha scritto:

Erika,

One possible approach...
If DCount("*","Table_Customers","CustomerNumber=" &
YourProposedNumber) = 0 Then
' your error message
Else
' go ahead
End if

Hi!

thank you so much for your help. Just one question:

the "YourProposedNumber" is what I insert as expr1 in my query. But
how can I transpose the number from the query to the code behind the
form?

Now my code says:
Docmd.openquery etc etc.

I guess I now need to change it to SQL so that I use an inputbox
instead of Expr1???

Thanks again!

Erika

 

[Steve Schapel]

Erika,

I don't understand whatthe purpose of the query is, or how the query
will "know" what the Expr1 value will be. It is almost certainly
incorrect to refer to a query field for the value. Can you explain a
little more about the process?

 

[tsuby]

Steve Schapel ha scritto:

Erika,

I don't understand whatthe purpose of the query is, or how the query
will "know" what the Expr1 value will be. It is almost certainly
incorrect to refer to a query field for the value. Can you explain a
little more about the process?

You are right, I am sorry that my description of the problem is not
clear.

I have three tables

Table_Customers (PK Autonumber) related through "CustomerNr" to
TableB_Orders (PK Autonumber) related through "OrderNr" to
TableC_Products (PK Autonumber)

and 2 forms

Frm_Customer_order (--> Table_Customers + TableB_Orders)
Frm_Order_Product (--> TableB_Orders + TableC_Products)

What I want to do is being able to duplicate a record in
Frm_Order_Product but assign it to a different customer than the
original one.
Since the new data (CustomerNr and OrderNr) are inputted manually, I
need to make sure that:
1) CustomerNr is already existing in Table_Customers
2) OrderNr is not a duplicate.

I am afraid I am making the problem much complicated than it really is,
but I'm stuck and I don't know how to handle this.

In my newbie design I thought to make an append query, duplicating the
record in a temporary table, check if CustomerNr is in Table_Customers
and OrderNr is not duplicated and, if everything is as it should be,
move the new record to the TableB_Orders emptying the temporary table.
But it does not sound like a very good proceeding.

Thanks in advance for the help!

Erika

 

[tsuby]

Private Sub cmdDupe_Click()

Ok. I declare myself not only as a newbie, but also a stupid one.
The solution was there and I could not see it.

This is the new code I found, understood and copied:

===============
Private Sub SoloHeader_Click() 'Purpose: Duplicate the main form
record and related records in the subform.
On Error GoTo Err_Handler
Dim IntNuovo As Integer, strTitolo As String
Dim IntFinestraMsg As Integer, strMsg As String
Dim strSql As String 'SQL statement.
Dim lngID As Long 'Primary key value of the new record.
Dim strValue1 As String
Dim strValue2 As String

If Me.Dirty Then 'Save any changes.
Me.Dirty = False
End If
strTitolo = "Conferma di duplicazione testata"
strMsg = "Vuoi effettivamente duplicare la testata??"
IntFinestraMsg = vbYesNo + vbQuestion
IntNuovo = MsgBox(strMsg, IntFinestraMsg, strTitolo)
If IntNuovo = vbYes Then

strValue1 = InputBox("Inserisci il Nuovo CustomerNr")
strValue2 = InputBox("Inserisci il Nuovo OrderNr")

'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!CustomerNr = strValue1
!OrderNr.= strValue2
!Title = Me.Title
!Formato = Me.Format
!Tipo = Me.Type
!Genere = Me.Genre
'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for
the related records.
.Bookmark = .LastModified
lngID = !Contatore
'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"SoloHeader_Click"
Resume Exit_Handler
End Sub
===========================

It works more than perfectly for adding the record, but I still am not
able to perform the check with DCount:

If DCount("CustomerNr", "Tbl_Customers", "CustomerNr=" & strValue1)

0 And DCount("OrderNr", "Tbl_Orders", "OrderNr=" & strValue2) = 0 Then

But now I think it should be easier to see where I am actually making
the mistake.

Thanks again for being so helpful.

Erika

 

[Steve Schapel]

Erika,

If the CustomerNr and OrderNr fields are Text data type, then you need
extra 's in the where condition argument of the DCount, for example...
If DCount("*","Tbl_Customers","CustomerNr='" & strValue1 & "'")=0 And _
DCount("*","Tbl_Orders","OrderNr='" & strValue2 & "'") = 0 Then

 

[tsuby]

Steve Schapel ha scritto:

Erika,

If the CustomerNr and OrderNr fields are Text data type, then you need
extra 's in the where condition argument of the DCount, for example...
If DCount("*","Tbl_Customers","CustomerNr='" & strValue1 & "'")=0 And _
DCount("*","Tbl_Orders","OrderNr='" & strValue2 & "'") = 0 Then

Oh. I had not thought of that.
This thing with * and '' and so on it's still a bit confusing to me. I
have to study (MUCH) more.
I am at work now, but I will try as soon as I am back home.

Thanks again for the help!

Erika

 

[tsuby]

Dear Steve,

I just wanted to let you know that everything is now working, Dcount as
well
WOW

I am so happy!

Thanks

Erika

 

[Steve Schapel]

Very nice. Thanks for letting us know, Erika. Best wishes with the
rest of your project.