How to auto populate form fields? (Any type of field in general)

[ThornofSouls]

Hello i wonder if it is possible to auto populate a number field on a from?
Yes it is a number field in the table.

My intention with this function is to make the system more user friendly and easier to use.

We have two forms, and two tables, frmA, frmB, tableA and tableB.
Table A represents the customers and table B represents orders.


Form A is a detailed view of the customer, with all the contact information and summarized product orders. (Just the most essential for an overview.)
Form B adds new orders or views a more detailed view on the order, with related products, company name, exetra.


The ideé is simple, pass the custID from formA to fromB and insert the CustID to the field which will tie the table records together.
The thought was simple, take the ID from formA and pass it to fromB and feed the field on the form which ties tabelB with tableA.

This is the code i use to pass the ID from formA:

    Dim varID As Variant    varID = Me!CustID
    DoCmd.OpenForm "frmName", acNormal, "", "", acFormEdit, acDialog, OpenArgs:=varID

FromB receives the IDnumber from fromA.
Now when we got the CustID on fromB all we have to do is update the txtCustID field with the value, so this record with be tied to the related record on tableA.

On formB we have:

• OrderID(pk)(hidden for users)
• txtCustID(fk)(hidden for users)
• -other fields-

In order to set stsCustID to the same as CustID i have trid the following code.

Debug.Print "CustID: ", Me.txtCustID
Debug.Print "CustID.value: ", Me.txtCustID.Value
OpenArgs = Me.txtCustIDOpenArgs = Me.txtCustID.Value
OpenArgs = Me!txtCustID
OpenArgs = Me!txtCustID.Value

The debug.print returns a 0, both of them. (I did expect a null value as null because the field is literary empty, well so i thought.)
The others returned: "This property is write protected and can not be specified."
So i changed the field to a combobox. My ideé here was so have all the customers in the combobox and filter out the openargs value where the openargs value equals the CustID in the CustID table. Then the combobox would "autoupdate" itself in a way. But i recived this error "This property is write protected and can not be specified." again and now i'm out of options.

Any advice and pushes in the right direction is very welcome.
//ThornofSouls.

[ranman256]

you can use a 'working' table, run an append query to transfer all the client data to this table.
then the user would click a save button to transfer (query) this data somewhere.
or
dont use the 'working' table and just grab the clientID then open an unboud form on this clientId
a subform shows all the bound data for the client.

[NoellaG]

Hi,

a possible solution: After opening frmB from frmA (I gather you use an action button here), set the default value of txtCustID on frmB to the current customer Id of frmA. Something like: forms("frmB").txtCustID.defaultvalue = me.txtID

[ThornofSouls]

I'm not familiar with the term "working table" and i haven't just append queries that much so far. I have to look into that a bit and a short summerizon or explanation would be appreciated.

This may was worth mentioning before but the remation from tableA and tabelB is one to many.

I'm using an unbound form to show detailed information about the client based on a query and it works great. But then i have a source to refer to, in this case in don't, well not at the destination.
It sounds like that is what you are telling me to do.

[ThornofSouls]

I'll try that NoellG, thx for the advise.

[ThornofSouls]

It works but access crashes when i close the form. I wonder if the error is related to the default value i assigned or the save function i written.
hehe one sulution results in something else that don't work either, really amusing.^^
I think i will rewrite the entire save function with the criterias that may work better then look for a misspelling or character miss match. xD

[ThornofSouls]

Interesting, whenever i run the command ( DoCmd.Close acForm, Me.Name ) Access crashes.

[ssanfu]

Interesting, whenever i run the command ( DoCmd.Close acForm, Me.Name ) Access crashes.

Must be some corruption in the dB.


So much easier to use a main form/sub form arraignment. Main form has some customer info, sub form has the orders.
Link the forms on the PK/FK fields. Don't have to mess with openargs, unbound forms.......


Select a customer in the main form, enter orders into the sub form. Easy as that.

[ThornofSouls]

I did run a few test and access dos only crash when i try to close the from through my saveCriteria function. So i'm not really sure on whats going on. It may is corruption and i may approach this the wrong way or i have to do something more. I don't know i just found this very interesting.
Delete the form and redo the form did not work, i have to see what happens if i bring this into a new db.

Here is the code i have on the form.

Option Compare Database
Option Explicit


Private Sub btnClose_Click()
    Me.Undo
    Me.Refresh
    DoCmd.Close acForm, Me.Name, acSaveNo
End Sub


Private Sub btnSave_Click()
    Dim intStatus As Integer
            intStatus = saveCriteria()
End Sub


Private Function saveCriteria()
On Error GoTo Err_handler
    Dim CritItems As New Collection
    Dim CritItem As Variant
    Dim ValidationCrit As Integer
            ValidationCrit = 0
    Dim Validation As Boolean
    
    With CritItems '>>> 4 Items <<<'
        .Add Me.cboClientID
        .Add Me.cboSysRegCategory
        .Add Me.txtRegDate
        .Add Me.txtSysRegID
    End With
    For Each CritItem In CritItems
        If Not IsNull(CritItem) Then
            ValidationCrit = ValidationCrit + 1
            CritItem.BackColor = RGB(252, 230, 212)
        Else
            ValidationCrit = ValidationCrit - 1
            CritItem.BackColor = RGB(255, 179, 179)
        End If
    Next CritItem
    Select Case ValidationCrit
        Case Is <= 3
            Validation = False
            ValidationCrit = -1
            Me.Dirty = True
        Case 4
             intResponse = MsgBox("All criterias fullfild")
            Validation = True
            Me.Dirty = False
        Case Else
            Validation = False
            ValidationCrit = -1
            Me.Dirty = True
    End Select
    intResponse = MsgBox(Validation)
    If Validation = True Then
            intResponse = MsgBox("Hardcore muther fuckers!!!")
            DoCmd.Close acForm, Me.Name, acSaveYes
    Else
        Resume Err_handler
    End If
Exit_Process:
    Me.Refresh
    Exit Function
Err_handler:
'    Select Case ErrNum
'    Case Else
'        Select Case CotumErr
'        End Select
'    End Select
    Resume Exit_Process
End Function


Private Sub btnUnDo_Click()
    Me.Undo
End Sub


Private Sub Form_Open(Cancel As Integer)


If Nz(Me.OpenArgs) = 0 Then
Else
    Me.cboClientID.DefaultValue = OpenArgs
End If
End Sub