Update data in table based on combo box selection (1 Viewer)

M

mba_110

Registered User.
Local time
Yesterday, 20:36
Joined
Jan 20, 2015
Messages
280
Hi

I want to update table's field based on combo box selection, all table fields are on same form of combo box.

First when i select combo box text fields and other combo boxes on form should be update/change as per cboEmpID selection and in same if i need to change in any of text field data that should be enabled to accept the changes, so the record in table will be updated based on my changes on text fields.

All control source is from tblEmployee field, cboEmpID is the combo box that will be trigger for all changes in all data fields on form.

It sound little complicated, hence i attached strip version to understand clearly.

i need to figure out how i can do it with all above points, what is that strategy?

Any help will be appreciated.
 

Attachments

  • Test1.zip
    73.6 KB · Views: 194
M

mba_110

Registered User.
Local time
Yesterday, 20:36
Joined
Jan 20, 2015
Messages
280
It has text boxes also which cant be used in cascading combo box, i need amendable information upon main combo selection which will store the information in table based on my changes in combo box as well as text box in same form.

for example if i select employee ID 1 in cboEmpID than all his details and text boxes or combo boxes field on form should change to this employees information and this information should be editable to take effect in to table.


Hence, i attached the strip version of DB to make it clear.
 
Last edited:
MajP

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 23:36
Joined
May 21, 2018
Messages
8,527
I think something like this. Two different versions. Bottom line the CboEmpID needs to be unbound and used to search and move to that record.
 

Attachments

  • Test2.accdb
    764 KB · Views: 281
  • Test3.accdb
    1.7 MB · Views: 217
Last edited:
M

mba_110

Registered User.
Local time
Yesterday, 20:36
Joined
Jan 20, 2015
Messages
280
It is somewhat ok with editing purpose, but when making new entry same problem is their he is asking "Index or primary key cannot contain the null value" that means it is not taking new entry in tblemployees?

Anyways i have reached half way, remaining is new record entry in this form.

I think some ref coding is required to make tblEmploymentContracts [ContractID] available with recent tblEmployees [PK] at same time so that it will not show this message while recording new entry.

Thanks for your help, if you could guide me for this also that will be so kind of you.
 
MajP

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 23:36
Joined
May 21, 2018
Messages
8,527
Normally the PK for the tblEmployee would be an autonumber not a number. Unless you have a hard reason to have a manual number simply change it to an autonumber and your problem will go away. If you really want to type in a manual number then you have to add it to your form bound to the employeeID. So the combo is only used for searching.
 
M

mba_110

Registered User.
Local time
Yesterday, 20:36
Joined
Jan 20, 2015
Messages
280
I give a try to following after update event in combo box but somewhat what criteria i need to add in below to call the table on my datasheet on form it should be changing as per the combo box value its first step for now.

Code: 
Option Compare Database

Private Sub CboOptions_AfterUpdate()
If Me.CboOptions.Value = "Employee" Then
DoCmd.OpenTable , "tblEmployees"
ElseIf Me.CboOptions.Value = "Contact" Then
DoCmd.OpenTable , "tblContacts"
ElseIf Me.CboOptions.Value = "Qualification" Then
DoCmd.OpenTable , "tblQualifications"
ElseIf Me.CboOptions.Value = "Passport" Then
DoCmd.OpenTable , "tblPassports"
ElseIf Me.CboOptions.Value = "Resident ID" Then
DoCmd.OpenTable , "tblResidentID"
End If
End Sub

Please have a look and give me direction for next step.

thanks.
 
MajP

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 23:36
Joined
May 21, 2018
Messages
8,527
My guess is that the value of the combobox is not what you think. Because that looks OK
To debug put this

Code: 
Private Sub CboOptions_AfterUpdate()
[B][COLOR="Red"]Msgbox Me.CboOptions.value[/COLOR][/B] 
If Me.CboOptions.Value = "Employee" Then
DoCmd.OpenTable , "tblEmployees"
ElseIf Me.CboOptions.Value = "Contact" Then
DoCmd.OpenTable , "tblContacts"
ElseIf Me.CboOptions.Value = "Qualification" Then
DoCmd.OpenTable , "tblQualifications"
ElseIf Me.CboOptions.Value = "Passport" Then
DoCmd.OpenTable , "tblPassports"
ElseIf Me.CboOptions.Value = "Resident ID" Then
DoCmd.OpenTable , "tblResidentID"
End If
End Sub

What is returned in the Msgbox? If not what you expect try changing the bound column.
 
M

mba_110

Registered User.
Local time
Yesterday, 20:36
Joined
Jan 20, 2015
Messages
280
Thanks for your help.

however, the row source in my combobox is values which is "Employee";"Resident ID";"Passport";"Qualification";"Contact" and row source type is values, control source is empty this is the trigger point i think.

my datasheet name is tblDataSheet (Unbound) which will show the table's data after i select the value in above combobox that i need to link with combobox to show the coded table upon selection of its value in combobox.

No effect of msgbox however i receive the error message telling "Compile error: argument not optional"

for your ref all these are in one form only which is frmAddEmployeesData.


in second sernario i try to link single table which is tblEmployees to datasheet on form for display the records and i quote.

Code: 
On Error GoTo Proc_Error
If IsNull(Me.CboOptions) Then
   Me.tblDataSheet.Form.Filter = ""
   Me.tblDataSheet.Form.FilterOn = False
Else
  Me.tblDataSheet.Form.Filter = "[EmpID]=" & Me.CboOptions
  Me.tblDataSheet.Form.FilterOn = True
End If
Proc_Exit:
   Exit Sub
Proc_Error:
   MsgBox "Error " & Err.Number & " in setting subform filter:" & vbCrLf & Err.Description
   Resume Proc_Exit
End Sub

This is just try but (unsuccessful) for one table but actually i need all 5 tables on same datasheet with each time change in comb box value table should be changed in datasheet, i am completely lost how i can do it.
 
Last edited:
MajP

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 23:36
Joined
May 21, 2018
Messages
8,527
Can you post a demo again. The purpose of the msgbox was to see if your procedure was returning the correct value. If you are saying it does not pop up, then you have other issues. There is no problem with a combobox without a rowsource, so you are OK there.
 
M

mba_110

Registered User.
Local time
Yesterday, 20:36
Joined
Jan 20, 2015
Messages
280
I have posted demo.

on the other hand i could process to do it with individual forms which is quite easy, but i need it in one form for all the tables to open and added at same place rather then making individual subforms for adding new records.


Thanks for your help.
 

Attachments

  • Test1.zip
    100.6 KB · Views: 142
MajP

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 23:36
Joined
May 21, 2018
Messages
8,527
Is this what you mean. I am not sure I understand
Code: 
Private Sub CboOptions_AfterUpdate()
    If Me.CboOptions.Value = "Employee" Then
        Me.tblDataSheet.SourceObject = "table.tblEmployees"
        'DoCmd.OpenTable "table.tblEmployees"
    ElseIf Me.CboOptions.Value = "Contact" Then
        Me.tblDataSheet.SourceObject = "table.tblContacts"
        'DoCmd.OpenTable "tblContacts"
    ElseIf Me.CboOptions.Value = "Qualification" Then
        Me.tblDataSheet.SourceObject = "table.tblQualifications"
        'DoCmd.OpenTable "tblQualifications"
    ElseIf Me.CboOptions.Value = "Passport" Then
        Me.tblDataSheet.SourceObject = "table.tblPassports"
        'DoCmd.OpenTable "tblPassports"
    ElseIf Me.CboOptions.Value = "Resident ID" Then
        Me.tblDataSheet.SourceObject = "table.tblResidentID"
        'DoCmd.OpenTable "tblResidentID"
     Else
        Me.tblDataSheet.SourceObject = ""
     End If

This brings each table intor your subform based on the combo choice.
 
M

mba_110

Registered User.
Local time
Yesterday, 20:36
Joined
Jan 20, 2015
Messages
280
Thanks for your help really appreciated.

Now i need 1st four fields of table in datasheet should be control of unbound text boxes in form

for example if i select employee in cboOptions the tblEmployee will appear in datasheet that is done now, but i need 1st four fields of tblEmployee let say [EmpID],[EmployeeName],[ContractID],[Gender] should be control source of Textbox 1, textbox 2, textbox3, textbox4 which should enable be to add new entry.

[EmpID] = textbox 1
[EmployeeName] = textbox2
[ContractID] = textbox3
[Gender] = textbox4

this example for tblEmployee, but i need all tables whatever showing in tblDatasheet on form its 1st four fields should be linked to four texboxes to add new entry in table that means each time textboxes control should change to tblDatasheet's 1st four fields.

I know as you said it can be done by doing autonumber but i cant do it with all tables even with employee table because number keep increasing without actual use on every time i tested so from last number to new record their will be huge serial number gap which cant terminated with autonumber field.

Many thanks for your kind help
 
You must log in or register to reply here.

Similar threads

S
Unbound combo box is clear after making selection
Replies
10
Views
153
theDBguy
theDBguy
H
Update subform based on main form combo box
Replies
5
Views
360
cheekybuddha
cheekybuddha
D
Solved Synchronising a List Box to a Combo Box
Replies
9
Views
595
davegoodo
D
A
Solved Two Combo Boxs for Name & Number
Replies
4
Views
359
accessonly11
A
A
Loop up or Combo Box
Replies
19
Views
620
June7
June7

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom