Moving fields

[Staelens30]

I have tried alot of different code and i am still not able to get what i am trying to accomplish i am posting a copy of db. I would like the fields below first name 2 and last name 2 to move up when the tow buyers checkbox is left blank.

View attachment Joshua1.mdb

 

[MarkK]

You're paddling upstream if you have two items in one record. The way a database works is that one record in a customer table might be related to many records in an order table. But one record should only ever represent one single indivisible thing.

 

[scalextric59]

You need something like this. However, before doing any fancy stuff the form must be working properly,all the controls properly named, normalization (as lagbolt mentioned), etc. Then, you can start all this secondary coding.

Private Sub cbtwobuyers_AfterUpdate()
lblfirstname2.Visible = cbtwobuyers
txtfirstname2.Visible = cbtwobuyers
lbllastname2.Visible = cbtwobuyers
txtlastname2.Visible = cbtwobuyers
If Not cbtwobuyers Then
Me.D1.Top = lblfirstname2.Top
Me.D2.Top = txtfirstname2.Top
Me.D3.Top = lbllastname2.Top
Me.D4.Top = txtlastname2.Top
Else
Me.D1.Top = 1.5417 * 1440
Me.D2.Top = Me.D1.Top
Me.D3.Top = Me.D1.Top
Me.D4.Top = Me.D1.Top

End If
End Sub

Private Sub Form_Open(Cancel As Integer)
Call cbtwobuyers_AfterUpdate
End Sub

 

[Kryst51]

After normalization, wouldn't using a continuous subform with no border on the control be an easier way, you wouldn't have to code anything? Just set the subform to "can grow" or "can shrink", And use the check box on the main form to filter the subform, one record or all records, or something like that.

 

[missinglinq]

... set the subform to "can grow" or "can shrink"...

Can grow and Can shrink is really designed for use on Reports. It only works for Forms if you are printing the Form, not merely displaying it. And printing Forms, of course, is generally considered to be poor idea.

Linq ;0)>

 

[boblarson]

Your data is not normalized. You shouldn't have Name1 and Name2, etc. like that. If you have multiple people to be associated with a record you need a junction table and then use a subform. See here for more about normalization.

 

[Kryst51]

Can grow and Can shrink is really designed for use on Reports. It only works for Forms if you are printing the Form, not merely displaying it. And printing Forms, of course, is generally considered to be poor idea.

Linq ;0)>

Aha, I see.... I think somewhere deep down inside of me that I really did know that.

 

[MarkK]

Also, you don't need to explicitly hide labels that are children of other controls. If you hide the parent control the children are hidden automatically.

 

[Staelens30]

I hope this is what you are refering to when you say noramlize the tables.


View attachment Josh1.mdb

 

[boblarson]

Close but not quite there yet.

1. You should have only ONE customer table. It lists all customers regardless of what type they are.

2. The TYPE (primary or secondary) would be included in the Deal Information table. Personally, I would have a juction table to add customers - that way you would not be limited to only 2 on a deal (but if 2 can only be there then having two fields in the Deal Information table would be the way to go and then you link two instances of the Customers table; one for each field).

3. Again you have Primary Salesman and Secondary Salesman in the Deals table. I would normally have that as a junction table as well so you can add more than 2 salesmen if necessary.

4. The delivery information shouldn't be linked to Customers. In fact it should have the DEAL ID in it and linked to the deal. You would not need the customer ID in that table as it is linked to the Deals table which then has the customer ID in it.

5. Your tables should not have Customer ID as the primary key in each of the tables. They should have their own - otherwise you will be limited to ONE DEAL, ONE DELIVERY per customer - EVER.

Also you should not use spaces or special characters in your field or object names (I've changed those) as it will make your life way easier when it comes to doing queries and anything else as you won't need square brackets around everything.

See attached revised tables and relationships.

 

[boblarson]

Actually I modified this a bit. Moved the EmployeeTypeID into the EmployeesDeals table from the Employees table and added a field IsPrimary to it so you can check who the primary salesman was.