unbound text box controls with combo box selection

[mba_110]

Hi i am struct with following additional version of my code.

I am trying to call first 3 fields of table upon after update event of combobox on my form, but it gives me error i think i am missing some quotation marks around the reference in below code or may be more.

Private Sub CboOptions_AfterUpdate()
 If Me.CboOptions.Value = "Employee" Then
        Me.tblDataSheet.SourceObject = "table.tblEmployees"
        Me.textbox_1.Value = "tblEmployees","EmpID"
        Me.Textbox_2.Value = "tblEmployees","Fullname"
        Me.Textbox_3.Value = "tblEmployees","Surname"
       
    ElseIf Me.CboOptions.Value = "Contact" Then
        Me.tblDataSheet.SourceObject = "table.tblContacts"
        Me.textbox_1.Value = "tblEmploymentContracts","ContractID"
        Me.Textbox_2.Value = "tblEmploymentContracts","EmployeeID"
        Me.Textbox_3.Value = "tblEmploymentContracts","HireDate"
       
    ElseIf Me.CboOptions.Value = "Qualification" Then
        Me.tblDataSheet.SourceObject = "table.tblQualifications"
        Me.textbox_1.Value = "tblQualifications","QualificationID"
        Me.Textbox_2.Value = "tblQualifications","EmpID"
        Me.Textbox_3.Value = "tblQualifications","Certification"
        
    ElseIf Me.CboOptions.Value = "Passport" Then
        Me.tblDataSheet.SourceObject = "table.tblPassports"
        Me.textbox_1.Value = "tblPassports","Passport_ID"
        Me.Textbox_2.Value = "tblPassorts","Number"
        Me.Textbox_3.Value = "tblPassorts","EmpID"
        
    ElseIf Me.CboOptions.Value = "Resident ID" Then
        Me.tblDataSheet.SourceObject = "table."ResidentID"
        Me.textbox_1.Value = "tblResidentID","ID" 
        me.textbox_2.value= "tblResidentID","Resident_ID"
        Me.Textbox_3.Value = "tblPassorts","SponsorID"
        
     Else
        Me.tblDataSheet.SourceObject = ""
     End If
End Sub

I wanted these three unbound text boxes act accordance to the table i have referenced in above code to enter the new entries (Last row) on each table for these table fields only.

Many thanks in advance for your help.

 

[June7]

Why are you trying to set textbox Value property to table and field names? This makes no sense.

And tblDataSheet is name of form?

 

[mba_110]

tblDatasheet is on same form where i am calling various table at same Datasheet object just to avoid too many subforms.

Upon selection of my cobobox relevant table will appear in tblDatasheet object which is coded perfectly no worries about that, i need the first 3 fields for each selected table fields to be updated using unbound text boxes on form.

for that i am trying to select control of those fields to unbound text boxes in code but i am not aware how to code it perfectly.

 

[June7]

So tblDataSheet is a subform container control? It's SourceObject is a table? SourceObject must be a form. Then code will reference fields and controls in that form.

A better name for the container would be ctrData.

Me.Textbox1 = Me.ctrData!EmpID

 

[JHB]

Can't you use the below code instead, (not tested)?

        ...
        Me.tblDataSheet.RecordSource = "tblEmployees"
        Me.textbox_1 = "EmpID"
        Me.Textbox_2 = "Fullname"
        Me.Textbox_3 = "Surname"
        ....

 

[mba_110]

its working but, textboxes filled with first record, but i wanted new record (Blank to add new).

Since EmpID is number field and not autonumber this might be the issue.

How to solve it, i cannot change EmpID field to Autonumber because their is jumping in serial without actual use i don't want to be get messed with.

Don't we get the tblEmployees field control directly on these boxes and same for other tables once i select different table from combobox value?


If necessary i can terminate the subform to make my life easy just combox and 3 unbound text boxes which will add new record in selected table.

 

[JHB]

... but i wanted new record (Blank to add new).

That is not what you code is set up to!

 

[June7]

If you want a sequential EmpID then you must assign this value. Automating the generation of a custom unique identifier requires code that pulls the last created value and increments to next value in series. This is a common topic.

 

[mba_110]

I am sorry if i make things little difficulte to understand.

I need to make new entry regardless of serial in table new entry will come at button itself, i just need fields control with new entry mode not before not after and not first or last only new entry to enter.

I am really struggling to get done, and need your help if i miss any part or reference in below incomplete code.

Private Sub CboOptions_AfterUpdate()
 If Me.CboOptions.Value = "Employee" Then
    'To get controls of these 3 tblEmployees fields in below text boxes with new entry to make (which should record inputs in table).
       Me.textbox_1.Value = "tblEmployees",[EmpID]
       Me.Textbox_2.Value = "tblEmployees",[FullName]
       Me.Textbox_3.Value = "tblEmployees",[Surname]
       DoCmd.RunCommand acCmdRecordsGoToNew
                  
End If
End Sub

 

[JHB]

Why do you think "Me.textbox??.Value = "tblEmployees",[EmpID]" is correct?

       Me.tblDataSheet.RecordSource = "tblEmployees"
        Me.textbox_1.ControlSource = "EmpID"
        Me.Textbox_2.ControlSource = "Fullname"
        Me.Textbox_3.ControlSource = "Surname"
        ...

 

[mba_110]

No more tblDatasheet directly calling from tblEmployees.

I don't know the exact calling method from table in event codes.

Just need to correct my code.

 

[June7]

If you want a blank new record then why are you trying to populate fields? Using .Value property is putting data in record. It appears you want to dynamically change SourceObject of subform container and use tables as SourceObject. Since tables do not have textboxes, exactly what textboxes are you referencing? Are they on main form?

 

[mba_110]

Text boxes are on main form their is no subform now before I try it with object property subform but it became more complicated that's why I drop subform, now I am trying to link my 3 unbound text boxes which is 1,2,3 on main form to get control of tblEmployees [EmpID],[Fullname],[Surname] but it has to go for new record.

Their is Combobox on main form which is trigger for selecting table from it's value list for now I am trying for employee table if I succeed than I will add another 3 tables to combo box value list.

This excersice to avoid too many subforms, instead of these I am trying the single form with combo box to dynamically select the table field to enter new entries.

For example if select Employees in combo box then "tblEmployees" fields should get control for above 3 fields in 3 unbound boxes and same for other combo box values to the job for other referenced tables.

I have 10 tables and If I want to add new entry I will use this form just i will select the value in combo box and selected table fields will automatically came to text boxes
control to record new entry remember not old records i need only new entry to add in table.

 

[June7]

If you are dynamically changing form RecordSource then you need to dynamically change each textbox ControlSource property.

Me.Textbox1.ControlSource = "EmpID"

Then move to new record row.

 

[mba_110]

Thanks guys i got it what i required, this code is working now and i am happy that i achieve something.

Private Sub CboOptions_AfterUpdate()
 If Me.CboOptions.Value = "Employee" Then
 Me.Subform.SourceObject = "table.tblEmployees"
    RecordSource = "tblEmployees"
       Me.textbox_1.ControlSource = "EmpID"
       Me.Textbox_2.ControlSource = "Fullname"
       Me.Textbox_3.ControlSource = "Surname"
end if
end sub

Now extending it to another level/form (frmEmloyeeEntries) where i have new button which will bring me up this form (frmAddNewRecords) on this form as you know their is combo box with list of values, in that value their is "Employee" and i want when i click new button on frmEmployeeEntries this form which name frmAddnewRecords should open with selected value of "Employee" in cboOptions on frmAddnewRecords and user should not be able to change combo box selection it should be kind of locking to button i have pressed in frmEmployeeEntries and same have to be done for other values from other form buttons.

below is the code in event of btnNew on frmEmployeeEntries but not getting above results.

Private Sub btnNew_Click()
DoCmd.OpenForm "frmAddNewRecords", acNormal, CboOptions = "Employee"
End Sub

 

[June7]

Then why use combobox? Just use a locked textbox and set its value. Use OpenArgs to pass value to the called form.

DoCmd.OpenForm "frmAddNewRecords", , , , , , "Employee"

Now code in frmAddNewRecords Open or Load event uses OpenArgs.

Me.cboOptions = Me.OpenArgs

 

[mba_110]

This code did not execute the job properly.

frmAddnewRecords open with cboOptions filled with "Employee" but not limited to this value i want users should not be able to change combobox value if they are navigating from origin form button.

Secondly when you select normally any value in combobox its appear all related references like subform fields events etc as per your coding. but with this openArgs the effect is not same like normal combobox selection however, combobox filled with what required but not effected other objects subform didnt come up fields not appeared just open form with highlighted value in combo box with no any other effect which is not exactly i want.

finally I am calling more than one table data in same form so combo box is what i wish to keep it as second option to use, and button control from Origin forms will have to work as well with restrictions for user not to select any other value in combobox .