VBA - Private and Public issues

[Wilkens]

Hello all,

Having some issues with VBA, i'm quite new at it so hopefully its a really simple fix.

Private Sub cmdsaveandclose_Click() CurrentDb.Execute "INSERT INTO BookingDetails (FirstName, Surname, Email, TelephoneNumber, JobTitle, Placeofwork, Area, Date1, Date2)" & _ " VALUES('" & Me.txtfirstname & "','" & Me.txtsurname & "','" & Me.txtemail & "','" & Me.txttelephonenumber & "','" & Me.txtjobtitle & "','" & Me.txtplaceofwork & "','" & Me.txtareabox & "','" & Me.txtdate1 & "','" & Me.txtdate2 & "')" cmdclear_Click 'clear form DoCmd.CloseDatabase 'close database End Sub

<tbody>
</tbody>

Ok, so this code works well and i'm happy with it, but this is on a form called 'Form4BookingForm'. This form is where everyone enters their details as you can probably tell from the code. I want this to go to another Form where they confirm all their details as a couple of other bits are added from other forms on the database. On this confirmation form 'Form5Confirmation' i want a button to link to back to Form4 to save all the details into my table. Now I've tried several things and everything doesn't work. The simplest one i have found is:

Private Sub cmdsave_Click() Form4BookingForm.cmdsaveandclose_Click End Sub

<tbody>
</tbody>

Now in order for this button to work i've read that i have to change 'Private Sub' to 'Public Sub' on both VBA codes....... this is where the issue is. If i change the main code to public it makes the code not work. But without it being public this button won't work...... any suggestions???

Thanks to anyone who might help

 

[xenou]

Try a public sub in the same form as the buttons:

Public Sub Do_SaveAndClose()
    Call cmdsaveandclose_Click()
End Sub

Private Sub cmdsaveandclose_Click()
    CurrentDb.Execute "INSERT INTO BookingDetails (FirstName, Surname, Email, TelephoneNumber, JobTitle, Placeofwork, Area, Date1, Date2)" & _
    " VALUES('" & Me.txtfirstname & "','" & Me.txtsurname & "','" & Me.txtemail & "','" & Me.txttelephonenumber & "','" & Me.txtjobtitle & "','" & Me.txtplaceofwork & "','" &     
    Me.txtareabox & "','" & Me.txtdate1 & "','" & Me.txtdate2 & "')"
    cmdclear_Click 'clear form
    DoCmd.CloseDatabase 'close database
End Sub

Then you can call the public sub from the other form, while not changing your button code at all. That might work.

 

[Wilkens]

Hi Xenou,

Thanks for the quick response. I just tried what you've put and still nothing . The button on 'Form5' you can click but nothing happens still. And if i go onto 'Form4' the button to save everything and do the above works :P. It just won't connect the two buttons for some reason. Is there a different way in which i could make them link???

Thanks

 

[xenou]

I would set a breakpoint on the button that doesn't work and step through the code. Then you can see what is really happening when "nothing" is happening.

Breakpoints, stepping through code, etc:
A Day in the Life: Programming: Excel VBA Debugging for Beginners

 

[Wilkens]

Thank you Xenou, but now its coming up with 'Run-time error 424: Object required'


Public Sub cmdsave_Click()
Form4BookingForm.Form.cmdsaveandclosepublic
End Sub

Public Sub cmdsave_Click()
Form4BookingForm.cmdsaveandclosepublic
End Sub


It highlights the the red text...... I've tried a couple of things and nothing is working. The button on the other form which this is trying to click does work which is a start so the public button you suggested is working For some reason the connection between them isn't working.

Any ideas??

 

[xenou]

Hi,
this looks like the name of a button:
Form4BookingForm.cmdsaveandclosepublic

What you really want to do is call the same code that the button calls. I've honestly forgotten at the moment how to access subroutines in forms. See if you can play around with it. I often start by just creating a subroutine in a form that pops up a message and seeing if I can get it to run from another form. If you can't get it to work, let me know - i'll be home later tonight and can have a closer look.