problem with adding new record into subform via VBA - urgent please help

[jaryszek]

Hi Guys,



it is me, once again

I have problem with adding values into my subform using vba code.
It is very difficult issue.

in attachment please see the sample database.

Instruction:

Log into database using Login: ljar01
Password: test

tbl_Korekty form will be displayed.

And now fill all required fields like below:



4 fields on the right from subform are fulfilled automatically.

And qlikbutton with magic wand symbol - subform will be fullfiled:




This button is used code:

Private Sub Button_MonDate_Click()


Dim dteDate As Date
Dim dteDateCounter As Date
Dim dteDateEnd As Date
Dim sqlString As String
Dim counter As Byte
Dim Money_value As Currency
Dim LiczbaRaportow As Variant
Dim Cause_ID_FK As Long
Dim Korekty_ID_FK As Long
Dim Slownik_ID_FK As Variant
Dim Status_name As Long


If IsNull(Me.Com_przyczyna) Or IsNull(Me.Com_topic) Or _
    IsNull(Me.Com_status) Or _
    IsNull(Me.TextBoxDfrom) Or IsNull(Me.TextBoxDTo) Or _
    IsNull(Me.Txb_slownik_id) Then


    MsgBox "Wszystkie pola muszą zostać wypełnione", _
    vbCritical, _
    "Anulowanie akcji"
    Me.SetFocus
    Exit Sub
End If




dteDate = Format([TextBoxDfrom], "MM/YYYY")
dteDateEnd = Format([TextBoxDTo], "MM/YYYY")


If dteDateEnd < dteDate Then
    MsgBox "Data korekty DO powinna być większa niż Data korekty OD)"
End If


dteDateCounter = dteDate


While dteDateCounter <= dteDateEnd
    counter = counter + 1
    dteDateCounter = DateAdd("m", 1, dteDateCounter)
Wend


LiczbaRaportow = Nz(DLookup("Liczba_raportow", "tbl_Causes", "Cause='" & Me.Com_przyczyna.Column(1) & "'"), 0)


If LiczbaRaportow = 0 Then
    LiczbaRaportow = Null
End If


Cause_ID_FK = Com_status.Column(0)


    With Me.[NowaOby]
    .SetFocus
    .Form![Status_date].SetFocus
    .Form!Cause_ID_FK = Cause_ID_FK
    
    On Error Resume Next
        RunCommand acCmdRecordsGoToNew
    On Error GoTo 0
    .Form!Liczba_raportow = LiczbaRaportow
    .Form!Status_date = Now()
    .Form!Data_korekty = Now()
    
    With Form_NowaOby
        .Com_status_n = Com_status.Value
        .Com_przyczyna_n = Com_przyczyna.Value
    End With
    
    DoCmd.RunCommand acCmdSaveRecord


    End With


End Sub

here i am adding new record into subform.
Problem is when do you wnat to add another record, The code:

   On Error Resume Next
        RunCommand acCmdRecordsGoToNew
    On Error GoTo 0

throw an error 2501: "runcommand action was cancelled"
My goal is to give possibility to user in order to choose dates, for example 2017-10-01
To 2017-11-01 (2 calendars icons). And after clicking magic wand 2 records with 2 dates should be inserted into forms (All other data should be the same)

Why this is not working. How can i do it in other way?

Please help,
Thank you in advance,
Best Wishes,
Łukasz Jarych

[ranman256]

you shouldnt need code. An append query will do this.

[jaryszek]

ranman256,

Thank you my friend.
Append query (insert into) is not working. (try my sample database please).

Why ?
Because If i am inputing new record into tbl_Korekty the currently inputed record is not saved.
So insert into statemant does not see Tbl_Korekty_ID record with matching key with field Korekty_ID_FK.

for example.
I am inserting new record.
Korekty_ID is "57" autonumber.
And if i am trying to create append query using VBA Korekty_ID number 57 does not exists yet.
If i add new record and go back into this current recorsd - i can use insert into statememt without any problems.

Add this code into VBA module:

sqlString = "Insert into NowaOby(Korekty_ID_FK,Cause_ID_FK, Status_name_ID_FK) VALUES (" & Txb_korekty_id.Value & ",1,1)"
DoCmd.RunSQL sqlString

error connected with this:


Please help,
Best wishes
Jacek

[ssanfu]

@Jacek

I tried to run your dB, but there are too many errors in the code.
Lots of combo boxes that are not on the form. And there is a fatal error in the form "Login".

There is a sub that is named "Private Sub Button_NewRec_Click()" but the button doesn't exist.
And there is this code:

Private Sub Button_back_Click()
    
    Me.Combo_pesel.Value = Null
    Me.Combo_sap.Value = Null

    Me.Combo_pesel.RowSource = "SELECT DISTINCT *  FROM QrySurnames"
    Me.Combo_sap.RowSource = "SELECT DISTINCT QryPersonID.Person_ID, QryPersonID.Employee_ID_FK_Person,QryPersonID.System, QryPersonID.Department FROM QryPersonID;"
    
End Sub

The button doesn't exist and none of the combo boxes exist on the form "tbl_Korekty" (poor name for a form )

[jaryszek]

Hi guys,

Steve thank you my friend for your help !
Sorry for mistakes in my application and for wasting your time.

I found the solution.

Subform data entry property was set to "yes" instead of "no".
And code was not working.

Now this code:

While dteDate <= dteDateEnd    With Me.[NowaOby]
        .SetFocus
        .Form![Status_date].SetFocus
    End With
    
    With Me.[NowaOby].Form.RecordsetClone
    .AddNew
        !Status_date = Now()
        !Data_korekty = dteDate
        !Cause_ID_FK = Cause_ID_FK
        !Status_name_ID_FK = 1
        !Korekty_ID_FK = Korekty_ID
        !Liczba_raportow = LiczbaRaportow
        !Kwota_korekty = Money_value
    .Update
    
    RunCommand acCmdSaveRecord
    End With
dteDate = DateAdd("m", 1, dteDate)
Wend

is working like a charm.
First of all i had to set up focus into this form and next i used code as you can see.

I am not closing this topic because i am wondering it is possible to use append query (insert into) into subform.
I think yes but only when you have not referential integrity set up.

Do you have maybe sample database ranman where you are using it ?
I am very courius.

Thank you once again Guys!
Jacek