|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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
|
|
Erika,
One possible approach...
If DCount("*","Table_Customers","CustomerNumber=" &
YourProposedNumber) = 0 Then
' your error message
Else
' go ahead
End if
--
Steve Schapel, Microsoft Access MVP
tsuby[ at ]libero.it wrote:
[Quoted Text] > 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 ha scritto:
[Quoted Text] > 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
|
|
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?
--
Steve Schapel, Microsoft Access MVP
tsuby[ at ]libero.it wrote:
[Quoted Text] >
> 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 ha scritto:
[Quoted Text] > 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
|
|
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)
[Quoted Text] > 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
|
|
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
--
Steve Schapel, Microsoft Access MVP
tsuby[ at ]libero.it wrote:
[Quoted Text] > 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
|
|
Steve Schapel ha scritto:
[Quoted Text] > 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
|
|
Dear Steve,
I just wanted to let you know that everything is now working, Dcount as
well :)
WOW
I am so happy!
Thanks
Erika
|
|
Very nice. Thanks for letting us know, Erika. Best wishes with the
rest of your project.
--
Steve Schapel, Microsoft Access MVP
tsuby[ at ]libero.it wrote:
[Quoted Text] > Dear Steve,
>
> I just wanted to let you know that everything is now working, Dcount as
> well :)
> WOW
>
> I am so happy!
>
> Thanks
>
> Erika
>
|
|
|