Add new record in datasheet view

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need a command button on form1 to open a form2 in datasheet view & add a
new record with data filled in based on data on form1, allowing the user to
either tweak the data in form2 at this point and/or accept the transaction.
I need all the records to show in form2, which is why I selected datasheet
view. When I put in the code to add a new record, it goes to DataENTRY view
& won't show the other records that are there. Is there a way to do this?
Basically, what I want is the equivilent of clicking on the little asterisk
and filling in those fields. If I don't put in the "add new record" code, it
just overwrites the records that are there. Please help!! Thanks so much
 
Put this in the VBA for the Open event of form2:

Private Sub Form_Open(Cancel As Integer)
DoCmd.GoToRecord acDataForm, "Form2", acNewRec
End Sub

This will show all existing records but put the focus on a new, blank record
at the bottm.
 
This all happens using a command button on form
Customer!CustomerTour(parent!Child)

I'm going to paste in here the code I have. When I tell it to open form1,
and the add a new record to form 5, I get an error message that the form5
isn't open.

When I tell it to open form5 & add a new record to form5 it says the form is
misspelled or doesn't exist.

Dim stLinkCriteria As String, form1 As String, form2 As String
Dim form3 As String, form4 As String, form5 As String

form1 = "frmAccount"
form2 = "frmTransaction"
form3 = "frmCustomer"
form4 = "frmCustomerTourDataEntry"
form5 = "Forms!frmAccountFrmTransaction"
stLinkCriteria = "[CID]=" & Me![CID]

'saves current record on customer tour form
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

'Opens account form to current customer
DoCmd.OpenForm form1, , , stLinkCriteria
'changes focus to subform frmTransaction (or form2)
Forms!frmAccount!frmTransaction.Form![TID].SetFocus

DoCmd.GoToRecord acDataForm, form5, acNewRec
Forms!frmAccount!frmTransaction.Form![TID] =
Form_frmCustomerTourDataEntry.TID
Forms!frmAccount!frmTransaction.Form![Type] = "Charge"
Forms!frmAccount!frmTransaction.Form![Charge-DR] =
Form_frmCustomerTourDataEntry.CustomerPrice

Also, Brian, I don't want to use the OPEN EVENT, as you suggested to place
this code, because that form may be opened at other times where I wouldn't
want this stuff to be going on. That's why I chose the on-click of the
command button.

Thanks again for your help. I really appreciate it.
 
Since I don't usually open/close forms programmatically, I'm not sure I have
much more to offer. However, are you sure that form5 is opened before you go
to the new record? It looks to me like you have not yet opened form5 when
this statement runs:

DoCmd.GoToRecord acDataForm, form5, acNewRec

I suspect that you need to use the OpenForm method before to GoToRecord...

Donna said:
This all happens using a command button on form
Customer!CustomerTour(parent!Child)

I'm going to paste in here the code I have. When I tell it to open form1,
and the add a new record to form 5, I get an error message that the form5
isn't open.

When I tell it to open form5 & add a new record to form5 it says the form is
misspelled or doesn't exist.

Dim stLinkCriteria As String, form1 As String, form2 As String
Dim form3 As String, form4 As String, form5 As String

form1 = "frmAccount"
form2 = "frmTransaction"
form3 = "frmCustomer"
form4 = "frmCustomerTourDataEntry"
form5 = "Forms!frmAccountFrmTransaction"
stLinkCriteria = "[CID]=" & Me![CID]

'saves current record on customer tour form
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

'Opens account form to current customer
DoCmd.OpenForm form1, , , stLinkCriteria
'changes focus to subform frmTransaction (or form2)
Forms!frmAccount!frmTransaction.Form![TID].SetFocus

DoCmd.GoToRecord acDataForm, form5, acNewRec
Forms!frmAccount!frmTransaction.Form![TID] =
Form_frmCustomerTourDataEntry.TID
Forms!frmAccount!frmTransaction.Form![Type] = "Charge"
Forms!frmAccount!frmTransaction.Form![Charge-DR] =
Form_frmCustomerTourDataEntry.CustomerPrice

Also, Brian, I don't want to use the OPEN EVENT, as you suggested to place
this code, because that form may be opened at other times where I wouldn't
want this stuff to be going on. That's why I chose the on-click of the
command button.

Thanks again for your help. I really appreciate it.

Brian said:
Put this in the VBA for the Open event of form2:

Private Sub Form_Open(Cancel As Integer)
DoCmd.GoToRecord acDataForm, "Form2", acNewRec
End Sub

This will show all existing records but put the focus on a new, blank record
at the bottm.
Click to expand...
Click to expand...
 
yes, I just happened to paste the sample of code after I played around with
opening different forms. I tried having it open form 1 with it giving me the
error I mentioned, then I tried having it open form5 with the error I
previously mentioned. I just can't figure out the magic combination. I
finally figured out a work-around by having it just open the transaction form
(using a different version of it) without the parent form. That seems to
work so thanks for your help.

Donna said:
This all happens using a command button on form
Customer!CustomerTour(parent!Child)

I'm going to paste in here the code I have. When I tell it to open form1,
and the add a new record to form 5, I get an error message that the form5
isn't open.

When I tell it to open form5 & add a new record to form5 it says the form is
misspelled or doesn't exist.

Dim stLinkCriteria As String, form1 As String, form2 As String
Dim form3 As String, form4 As String, form5 As String

form1 = "frmAccount"
form2 = "frmTransaction"
form3 = "frmCustomer"
form4 = "frmCustomerTourDataEntry"
form5 = "Forms!frmAccountFrmTransaction"
stLinkCriteria = "[CID]=" & Me![CID]

'saves current record on customer tour form
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

'Opens account form to current customer
DoCmd.OpenForm form1, , , stLinkCriteria
'changes focus to subform frmTransaction (or form2)
Forms!frmAccount!frmTransaction.Form![TID].SetFocus

DoCmd.GoToRecord acDataForm, form5, acNewRec
Forms!frmAccount!frmTransaction.Form![TID] =
Form_frmCustomerTourDataEntry.TID
Forms!frmAccount!frmTransaction.Form![Type] = "Charge"
Forms!frmAccount!frmTransaction.Form![Charge-DR] =
Form_frmCustomerTourDataEntry.CustomerPrice

Also, Brian, I don't want to use the OPEN EVENT, as you suggested to place
this code, because that form may be opened at other times where I wouldn't
want this stuff to be going on. That's why I chose the on-click of the
command button.

Thanks again for your help. I really appreciate it.

Brian said:
Put this in the VBA for the Open event of form2:

Private Sub Form_Open(Cancel As Integer)
DoCmd.GoToRecord acDataForm, "Form2", acNewRec
End Sub

This will show all existing records but put the focus on a new, blank record
at the bottm.
Click to expand...
Click to expand...
 
Back
Top