Saving in table (1 Viewer)

Kundan

Registered User.
Local time
Today, 08:43
Joined
Mar 23, 2019
Messages
118
In the attached DB when I type allocation the Dept. automatically appears. But I also want DEPT in Tbl_ALLOCATION-I to be updated simultaneously because all my queries are based on this field. How to achieve this?
 

Attachments

  • Database1.zip
    87.8 KB · Views: 76

June7

AWF VIP
Local time
Today, 07:43
Joined
Mar 9, 2014
Messages
5,423
Saving Dept in Tbl_ALLOCATION-I is duplication of data. There is no need to save Dept into Tbl_ALLOCATION-I - it can (and should) be retrieved in query that joins tables. There is no need to include ALLOCATION table in form RecordSource - just confuses things.

Dept value can easily be displayed on form by including Dep field in Allocation combobox RowSource. Expression in textbox can reference column by index.

=[ALLOCATION].Column(2)

Set Dept textbox as Locked Yes and TabStop No.

However, if you really want to save, use code (macro or VBA) in combobox AfterUpdate event:

Me!Dept = Me.Dept

or

Me!Dept = Me.ALLOCATION.Column(2)
 

Kundan

Registered User.
Local time
Today, 08:43
Joined
Mar 23, 2019
Messages
118
Thanks a Lot! GOD BLESS YOU!!!!!!!!!!!!!!!
 

Kundan

Registered User.
Local time
Today, 08:43
Joined
Mar 23, 2019
Messages
118
I put Me!Dept = Me.Dept in after update but it is not working in the attached DB.
 

Attachments

  • Database1.zip
    81.9 KB · Views: 76

June7

AWF VIP
Local time
Today, 07:43
Joined
Mar 9, 2014
Messages
5,423
Dept_AfterUpdate code is useless because users cannot edit in this textbox.

If you set combobox LimitToList to Yes then there is no need for code to check if allocation exists. If you must, then use DLookup() instead of opening recordset.

Again, I advise there is no need to save DEP value into Tbl_ALLOCATION-I. Did you consider other options I suggested?
 

Kundan

Registered User.
Local time
Today, 08:43
Joined
Mar 23, 2019
Messages
118
The problem is that by entering Dept & Allocation separately many mismatches occur. Hence I want Allocation to be entered and the corresponding Dept. will automatically be taken from the table (ALLOCATION). For this purpose I made the query for the form. But Dept field in table (DEPARTMENT & ALLOCATION) must be filled because there are many queries referring to this field. Changing all those queries is beyond my capacity. Hence I am trying some method by which I can also simultaneously fill Dept. in table.
 

June7

AWF VIP
Local time
Today, 07:43
Joined
Mar 9, 2014
Messages
5,423
How did you create the event procedure? It won't work because the property does not have [Event Procedure] selected and ALLOCATION is not name of combobox, it is Combo32.

I never said to enter Dept manually. Select Allocation only. Dept can be displayed and/or saved by methods I described.

I recommend:

1. not to include ALLOCATION table in form Recordsource, just bind to Tbl_ALLOCATION-I

2. bind textbox to DEPT, not DEP

3. set combobox properties:
Name: cbxALLO
RowSource: SELECT ALLOCA, DEP FROM ALLOCATION ORDER BY [ALLOCA];
LimitToList: Yes
ColumnCount: 2
ColumnWidths: 1";0"
AfterUpdate event: [Event Procedure]

4. combobox AfterUpdate code
Private Sub cbxALLO_AfterUpdate()
Me!DEPT = Me.cbxALLO.Column(1)
End Sub
 

Kundan

Registered User.
Local time
Today, 08:43
Joined
Mar 23, 2019
Messages
118
How did you create the event procedure? It won't work because the property does not have [Event Procedure] selected and ALLOCATION is not name of combobox, it is Combo32.

I never said to enter Dept manually. Select Allocation only. Dept can be displayed and/or saved by methods I described.

I recommend:

1. not to include ALLOCATION table in form Recordsource, just bind to Tbl_ALLOCATION-I

2. bind textbox to DEPT, not DEP

3. set combobox properties:
Name: cbxALLO
RowSource: SELECT ALLOCA, DEP FROM ALLOCATION ORDER BY [ALLOCA];
LimitToList: Yes
ColumnCount: 2
ColumnWidths: 1";0"
AfterUpdate event: [Event Procedure]

4. combobox AfterUpdate code
Private Sub cbxALLO_AfterUpdate()
Me!DEPT = Me.cbxALLO.Column(1)
End Sub


Thanks a lot!!!!!!

MAY GOD SHOWER HIS CHOICEST BLESSINGS ON YOU!!!!!!!!!!!
 

Users who are viewing this thread

Top Bottom