Append query

[izen]

Hi guys

I create the msgbox to warn the users that the fields to append are not complete the fields to the table

My append query has 4 fields ProductID, ProductName, Quantity, Cost
The problem is if i run the append query which are not complete 4 fields. the append query wont append the record in the table that we want.

So I just want to add the msgbox to warm users that their data is not complete to run the append query.

How can i do ?

cheers

 

[JoanneJames]

Sounds like you are trying to do something like this...


if len(nz(fieldname1),"") = 0 or len(nz(fieldname2),"") = 0 or len(nz(fieldname3),"") = 0 or len(nz(fieldname4),"") = 0 then
msgbox(....)
else
execute query
endif

 

[izen]

yes thats what I want thank you man

I have got another question. If I would like to put the field in the subform how can i do? cause when i simply put len(nz(Query1.fieldname1,"")) = 0 [Query1 is subform that I put in the main form. It doesnt work
the error said variable not defined

pls help!

thank you

 

[JoanneJames]

If you want users to pick from separate fields in subform:

Private Sub cmdAppendProduct_Click()
On Error GoTo Err_cmdAppendProduct_Click
Dim qrydef As DAO.QueryDef

Dim stDocName As String
stDocName = "qryAppendProduct"
'DoCmd.OpenQuery stDocName, acNormal, acEdit
If Trim(Len(Nz(Me.Products_subform.Controls("txtProductID")))) = 0 Or _
Trim(Len(Nz(Me.Products_subform.Controls("txtProductName")))) = 0 Or _
Trim(Len(Nz(Me.Products_subform.Controls("txtQuantity")))) = 0 Or _
Trim(Len(Nz(Me.Products_subform.Controls("txtCost")))) = 0 Then
MsgBox "All fields must be filled in. Please try again.", vbOKOnly, Me.Name
Else

Set qrydef = CurrentDb.QueryDefs(stDocName)
qrydef.Parameters(0) = Me.Products_subform.Controls("txtProductID")
qrydef.Parameters(1) = Me.Products_subform.Controls("txtProductName")
qrydef.Parameters(2) = Me.Products_subform.Controls("txtQuantity")
qrydef.Parameters(3) = Me.Products_subform.Controls("txtCost")
qrydef.Execute dbFailOnError

End If
Exit_cmdAppendProduct_Click:
On Error Resume Next
qrydef.Close
setqrydef = Nothing
Exit Sub
Err_cmdAppendProduct_Click:
MsgBox Err.Description
Resume Exit_cmdAppendProduct_Click

End Sub



another option:
you could enforce constraints on the backend, so that when users tried to add a record in the subform (which I assume is a bound subform), they will get a warning from the database itself that they cannot add a record.

You enforce constraints by indicating that a field is required and do not assign it a default value in the table or the frontend.

 

[izen]

hi thanks for you answer JoanneJomes
I will try
thanks again
===================================================
After I tried it doesnt work

If Len(Nz(ProductID, "")) = 0 Or Len(Nz(ProductName, "")) = 0 Or _
Len(Nz(Qauntity, "")) = 0 Or Or Trim(Len(Nz(Me.[qProductCost_subform].Controls("Cost")))) = 0 Then
....
else
....
end if
I filled every fields except Cost to test. the result comes out with error that "you entered an expression that as no value"

I had another idea
If Len(Nz(ProductID, "")) = 0 Or Len(Nz(ProductName, "")) = 0 Or _
Len(Nz(Qauntity, "")) = 0 Or Or DCount("*", "qProductCost", [ProductID]) = 0 Then

else

end if

qProductCost is query that was added in the subform in this query has 4 fields but I just set the critiria to check ProductID are filled in the query or not. If the user doesnt fill the cost, Dcount will return 0 and the msgbox will show. On the another hand if user fills the cost, Dcount will return > 0 the process will jump to else option.
Unfortunaltely it doesnt work either.

any suggestion ?

many thanks
=====================================================================
I tried to put ProductID number in Dcount, it works (136 is the ID of product that I want to check that user fill the cost field or not)

If Len(Nz(ProductID, "")) = 0 Or Len(Nz(ProductName, "")) = 0 Or _
Len(Nz(Qauntity, "")) = 0 Or Or DCount("*", "qProductCost", "ProductID = 136") = 0 Then

else

end if

Dcount return 0 if the user doesnt fill the cost field
but if I just put DCount("*", "qProductCost", [ProductID]) it returns all of the records in qProductCost.
how can I do to avoid the add the exactly number cause Its imposible to do like that

thanks

 

[JoanneJames]

Trim(Len(Nz(Me.[qProductCost_subform].Controls("Cost")))) = 0

How is Cost defined? does it have a default value of 0? If so, should remove default. Also,try changing to something like this...
'Check if value is null or = 0
Trim((Nz(Me.[qProductCost_subform].Controls("Cost"),0)) = 0

I would suggest you look at MS help - you should be able to find some examples on.

Sorry, I can't spend more time on this. Good luck.

 

[izen]

thank you for your time JoanneJomes
now I found out the way to dectect subform field

DCount("*", "qSumProductCost_subform", "RecipeID =" & [RecipeID]) = 0

thank you