Shipping address big problem 2

[ailyn]

I ahppen to be terrible with codes because everytime I try one on my own it gives me error so I really need help with this one.
I have to make an order where the shipping address depends on a checkbox customerctrl. The idea is that if the combobox is checked the code will make customerIdctrl.visible = true & editcustomer.visible = true
otherwise it won't show anything.
Afterwards when they select the customer from the combobox that shows (customerIdctrl) or add a new with (editCustomer) and then select it from the comobobox. That will set the customerId in the orders tbl.

Now I understand this is supposed to be simple but all the same it never works with me. I just write the code for the checkbox and it shows the customerIdctrl anyway. ???

by the way how exactly do you write if a value exists or not, is it with true or with null?
If [CustomerId] = ?? Then...

 

[ZmrAbdulla]

Two styles..

Private Sub chkBox_AfterUpdate()
    Select Case Me.chkBox.Value
    Case -1    'True
        Me.customerIdctrl.Visible = True
    Case 0    'False
        Me.customerIdctrl.Visible = False
    End Select
End Sub

Private Sub chkBox_AfterUpdate()
    If Me.chkBox.Value = True Then
        Me.customerIdctrl.Visible = True
    Else
        Me.customerIdctrl.Visible = False
    End If
End Sub

________________________________________________________
Zameer Abdulla
Help to find Missing people
There’s a world of difference between editorials and advertorials

 

[ailyn]

Thanks a looot!
that deserved a Star ^.^
I just had to change the Visibility Property off and then everything worked properly on load of the form. Thanks!

 

[PHV]

And a third style:
Private Sub chkBox_AfterUpdate()
Me.customerIdctrl.Visible = Me.chkBox.Value
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ailyn]

Thanks too PHV.
But if instead of checking the checkbox I want to check if customerIdctrl has an existant value (whose real name is customerId) to allow the visibility of CustomerIdctrl afterwards. How do I check if the value exists?

I tried the previous code and afterwards this:

Private Sub CustomerIdctrl_BeforeUpdate()
If Me.CustomerId.EOF Then
Me.CustomerIdctrl.Visible = True
Me.EditCustomers.Visible = True
Else
Me.CustomerIdctrl.Visible = False
Me.EditCustomers.Visible = False
End If
End Sub

But it simply doesn't work

 

[ZmrAbdulla]

I didn't undersatnd what you are going to do..
anyway code will be something like this...

Private Sub chkBox_AfterUpdate()
    If Not IsNull(Trim(Me.customerIdctrl)) Then
        Me.EditCustomers.Visible = True
    End If
End Sub

________________________________________________________
Zameer Abdulla
Help to find Missing people
There’s a world of difference between editorials and advertorials

 

[ailyn]

No, there is no checkbox anymore. Now I was talking of another form where if there is a customerId it will show the customername, address, pc, city,...
The question is how to check if a value from a table customerId = numeric (takes customer name from customer table)
exists or not. Afterwards I can just show the rest of the info by using something like

If customerId exists Then
Me.Name = Me.cname
Me.address = Me.caddress
Else
Me.Name = Me.sellername
Me.address = Me.selleraddress
End If

I thoguht that checking was made with BOF EOF but mine doesn't work.

 

[ZmrAbdulla]

You need to use DLookUp to find that... I can't tell in which event you need to check it because I don't know how do you fill the customerID
a sample code that is with combobox is below

Me.FirstName = DLookup("[FirstName]", "tblTable", "[ID]=" & cboLastName1.Value)

________________________________________________________
Zameer Abdulla
Help to find Missing people
There’s a world of difference between editorials and advertorials

 

[ailyn]

Er...No. I don't think that that is the one. Forget all the previous threads. The form orders has a field customerId that is the customerName from table customer. So the form works showing all the fields. If I have an independant textbox 'name' I'll filld it with customerName IF customerId exists otherwise it will fill it with sellerName (yes there is a sellerId on the table too). I think it's a simple request: How do you check the value 'customerId' exists or not.
how do you check if a value in a table has been filled/exists or not? It is a numeric value if you need to know.

 

[ZmrAbdulla]

Did you check the documentation for "DLookUp" in the help file?

DLookup("FieldName", "TableName", "Criteria = " & forms!FormName!ControlName)

for more on this

http://www.mvps.org/access/general/gen0018.htm

________________________________________________________
Zameer Abdulla
Help to find Missing people
There’s a world of difference between editorials and advertorials

 

[ailyn]

I'm sorry Zameer but that doesn't work in my form should I put it on a Private Sub format_current() or...? How do I put the If with that? Everything I try doesn't work.Er...I already mentioned I'm as newbie in VBA as one can be.
I tried this:

Private Sub Format_Current()
If Me.Orders.CustomerId.EOF Then
Me.shippedto = Me.Orders.CustomerId
Me.staddress = Me.Saddress
Me.stpc = Me.Spc
Else
Me.shippedto = Me.Orders.SellerId
Me.staddress = Me.Address
Me.stpc = Me.Postcode
End If
End Sub

The 'shippedto', 'staddress' and 'stpc' are independant textboxes. The customer & seller info is in the query of the form, as the fields ids are related they should work, but all what works is 'shippedto' shows a combobox where you can select the customer. I guess because the code doesn't get what it needs. But I really don't know how to trim the dlookup to show anything with if and it seems that If doesn't accept any dlookups inside...

 

[MajP]

I am a little confused, but if I read this right Orders is the table that your form has as a recordsource. "Me.Orders.Anything" will not work.
Me is your current form. If Orders is a table in the database, a table is not a property of a Form. The reason Me.shippedto works is that it is short hand for.
Me.Controls.Item("shippedto"). So "shippedto" is a control and is a member of the Controls collection which in turn is a property of your form.

However, each form has a Recordset property. So

dim rsOrder as dao.recordset
Set rsOrders = Me.Recordset

now you can work with the forms actual recordset
I do not think you want to know if the Recorset is EOF
But you want to know if a field customerID is null. But I might misunderstand you.
I assumme this is what you want to do. You check the record to see if the customerID exists, and if it does you set the form control "shippedto" equal to the customerID value in the record.

if not is null rsOrders.Fields("CustomerID") then
Me.shippedto = rsOrders("CustomerId")

If I misunderstood disregard all I said.

 

[ailyn]

Yes, Majp that's exactly what I meant!
The form proforma has tables proforma, orders, customers, sellers and a subform.
If there is a customer the item will be sent to him and not to the seller so the shippedto has to be the customer and the staddress the customer's address,etc. If there is not it will be the seller's...
Now, I tried your code:

Private Sub Format_Current()
Dim rsOrder As DAO.Recordset
Set rsOrders = Me.Recordset
If Not IsNull(rsOrders.Fields("CustomerID")) Then
Me.shippedto = rsOrders("CustomerId")
'Me.staddress = Me.Saddress
'Me.stpc = Me.Spc
Else
Me.shippedto = rsOrders("SellerId")
'Me.staddress = Me.Address
'Me.stpc = Me.Postcode
End If
End Sub

& it didn't work. Could it be the DAO? or do I have to change the Fields to something else? & afterwards how do I get the customer address from the customer table? for what I understood rsOrders is trimming the table orders from the form, isn't it? Should I carry out the same 'Dim' procedure with the other tables (sellers and customers)? & would they fit afterwards?
As you can see I'm completely lost T.T

 

[ZmrAbdulla]

Here is an example. Look for "Combobox - Populate a Form"

http://www.candace-tripp.com/pages/access_downloads.aspx

________________________________________________________
Zameer Abdulla
Help to find Missing people
There’s a world of difference between editorials and advertorials

 

[MajP]

ailyn,
The code I gave you was only meant to highlight the proper use of concepts like
Me.nameOfControl
Me.recordset
recordset.EOF
is null (me.nameOfControl)
recordset("fieldName")

You had multiple conceptual problems in those areas. The code was not meant to run, since I was not really sure of your set up.
One thing for the future. Use something called Hungarian notation it will make your life and someone looking at your code/db much easier.
Use these prefixes like these in your names (see other examples):

tables: tbl ex. tblOrders
forms: frm ex. frmEnterOrders
report: rpt ex rptOrdersReport
text box: txtBx ex. txtBoxSendTo

For example you have proForma the form, proforma the table, maybe proforma the field, maybe proforma the report, etc
If you did this, a user could quickly understand what you are referring to:
tblProforma
frmProforma
txtBxProformaValue
rptProformaOutput
etc.

When you said it did not run did you get an error? What was the error?
You do need to go to the vb window and set a reference to Microsoft DAO (Data Access Objects). Read the help on adding references.
When you say the form has tables "proforma, orders, customers, sellers". I assume that your recordsource for the form is based on a query linking these tables together. So in this query a single record has Order information, the order's customer information, the order's seller information, etc. If this is true then the code
set rsOrders = me.Recordset
returns this entire underlying query that the form is based on. Maybe a better name would be "rsOrderDetails" instead of "rsOrders" so as not to confuse you with the table Orders (in the future renamed to tblOrders).
So it should work, just tell me what the error is. If each order in you query has a sellers street address ("Address"), a customers street address("Saddress"), and an orders mailing address (stAddress) then:
rsOrderDetails("Sadrress")
should give you the street adress from the current record for the customer.
rsOrderDetails("Adrress")
For the seller

I assume you have a text box called staddress and its record source is the field "staddress" in the orders table. The sellers address is in a field in your underlying query called "Address". The proper way (using naming and full notation for clarity)
Me.txtBoxStAddress.value = Me.rsOrderDetails("Address").value

So how could we make this a lot easier to understand. Assuming the addresses are string Fields not memoFields. Her e is the value of good table names and field names.

tblOrders
strOrderMailAddress

tblSellers
strSellerMailAddress

tblCustomers
strCustomerMailAddress

So without any explanation I could read the following.
tblOrders("strOrderMailAddress")
I would know you have a table named orders, with a field of order Mailing addresses, and the information is a string.
Okay probably more information then anyone could want, but I was on a roll.

 

[ailyn]

Thaaanks a lot!!!
I could kiss you!

Well all that explains why hardly anybody in this forum understands me & why none of my codes work.

the frmProforma includes tblOrders, tblCustomers, tblSellers, tblOrderDetails & tblProformas. The are all joined by Ids fields.

and txtShippedto is just an independant text field in the form, i.e., it doesn't belong to any tbl.

the idea is to fill it with the appropiate info.

I'll work on what you gave me and check the info of Zameer too. Next week I'll tell you more. & thanks again!

 

[ailyn]

Well, in the end I couldn't make it work so I just changed it to both fields (selledId & customerId)present and only one visible thanks to this code:

Private Sub Form_Open(Cancel As Integer)

If IsNull(CustomerIdctrl) Then
CustomerIdctrl.Visible = False
SellerIdctrl.Visible = True ' No customer record
Else
CustomerIdctrl.Visible = True
EditCustomers.Visible = False ' Customer exists
End If
End Sub

I got it from another of my threads here that I needed for another form. But now I can use it here too XD