Prev: Calculations in Group Footer
Next: counting in fields
From: Geoff on 26 Jan 2010 21:56
Is this thread still active? Did you find solution?



John Spencer wrote:

I may or may not be available.
12-Jan-10

I may or may not be available. You might consider starting a new thread with
a brief overview of the problem and what you have tried.

I usually pick up threads I am involved in, but sometimes I miss them.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Ray C wrote:

Previous Posts In This Thread:

On Thursday, January 07, 2010 4:06 PM
Ray C wrote:

New Record
Could someone point me in the direction of come code that I could use to open
a New Record. I have a tbl_Suppliers which holds "SupplerNo" (Auto Number
Primary Key) "SupplierName", "Addr1", "Addr2" etc, etc. Everything I have
tried returns an error so far.

Thanks RayC

On Thursday, January 07, 2010 4:48 PM
Jerry Whittle wrote:

Are you trying to add a new record in a form or somewhere else?
Are you trying to add a new record in a form or somewhere else?

Have you tried the little button on the lower left near the word Record:
that looks something like >* with the asterick being yellow?

What have you tried and what was the result? Error message before opening
the new record? Error messages after entering data? Error message when trying
to save the new record?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Ray C" wrote:

On Thursday, January 07, 2010 6:22 PM
Ray C wrote:

Thank you for the reply. I want to add a new record to the Table.
Thank you for the reply. I want to add a new record to the Table. I do not
have navigation bar enabled on my form.I have two Combo boxes on the Form as
"cmb_FindBox_3" (finds a record by "SupplierName") and "cmb_FindBox_4" (finds
a record by "SupplierNumber" [Auto number Primary Key]). I want to trap out
an entry that is not in the List, tell the user and give the user the option
of adding it as a new record.

The lates bit of code I have tried is as follows :-

Private Sub Not_In_List(strNewData As String, Response As Integer, FindBox
As Integer)

On Error GoTo Err_Handler

Dim dbs As Database, rs88 As Recordset: Set dbs = CurrentDb
Set rs88 = dbs.OpenRecordset("tbl_Suppliers", dbOpenDynaset)

Me.AllowEdits = True: Me.AllowAdditions = True: Me.AllowDeletions = True

With rst88
..AddNew
![SupplierName] = strNewData"
End With
Some code "Not In List, do you want to add new record Y or N
if Y Then rst88.update etc etc
rs88.Close: dbs.Close

This crashes with "Object Required" at the line " ![SupplierName] =
strNewData"

I have missed out some fair chunks of code fpor brevity but my thoughts
being that
I would like the Whole New Record to be displayed as blank Fields with the
exception of the New Name that is in the "Supplier Name" Field. The Form
should (in my mind) be displaying the New Supplier Number (in the
"SupplierNumber" Box [new record Auto Number, Primary Key]), it should
display the New Name in the "Supplier name" fielg (obtained from
"strNewData") and all the other fields sho as being blank. I wouuld then tell
the program to continue into the "Edit" routine and allow the user to fill in
the blank fields as though they were editing a normal record. Clicking the
"Edit" button to return back to normal.
Oh; I just need to say that I would have included the following couple of
lines :-
'cmb_Find_Box_3.Visible = False: cmb_Find_Box_4.Visible = False
'Box_3.Visible = True: Box_4.Visible = True
To hide the Combo Boxes and reveal the "Bound" text boxes.

As I have said, I have not included chunks of code that I know I will need
to include but I hope that you understand what I am saying here.

Many Thanks

Ray C


"Jerry Whittle" wrote:

On Friday, January 08, 2010 8:24 AM
John Spencer wrote:

I use a general function to open a form to a new record.
I use a general function to open a form to a new record. I have posted it below
to give you the idea. You can either grab it and add it to a VBA module or
you can examine it and use the parts you need in your current code.

I would modify your code as follows.

Private Sub Not_In_List(strNewData As String, _
Response As Integer, _
FindBox As Integer)

Dim strMsg as String

On Error GoTo Err_Handler

Me.AllowEdits = True: Me.AllowAdditions = True: Me.AllowDeletions = True

strMsg = "Not In List, do you want to add new record?"

If msgBox(strMsg,vbYesNo) = vbYes then
Response = acDataErrAdded
sCmdAdd Me
Me.[SupplierName] = strNewData
End IF
ExitFunction

Err_Handler:
'Your error handling
End Function

'================= My general function =============
Public Function sCmdAdd(frmAny As Form) As Boolean
On Error GoTo Err_sCmdAdd

With frmAny
'Save changes if any in current record
If .Dirty = True Then .Dirty = False

'Set AllowAdditions to true if not already true
If .AllowAdditions = False Then
..AllowAdditions = True
End If

'Move to the new record
DoCmd.GoToRecord , , acNewRec

End With

Exit_sCmdAdd:
Exit Function

Err_sCmdAdd:
MsgBox Err.Number & ": " & Err.Description, , _
"sCmdAdd for " & frmAny.name & " - CmdAdd_Click"
Resume Exit_sCmdAdd

End Function

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Ray C wrote:

On Saturday, January 09, 2010 5:28 AM
Ray C wrote:

Hi JohnThanks for your hep but I must be doing something wrong here.
Hi John
Thanks for your hep but I must be doing something wrong here. I sorted out
the small slip between Sub and Function in the first part of the code but i
get an error in the second when it tries to execute "DoCmd.GoToRecord , ,
acNewRec" the error is "2499: You cant't usw the GoToRecord action or method
on an object in design view". However. nothing is actually in Design View as
far as I am Aware.
Any Thoughts? There is a lot that i would need to chop and change with the
routine but I wanted to get it working in it is "Virgin State" befor
attacking it. Thanks Ray C

"John Spencer" wrote:

On Saturday, January 09, 2010 11:15 AM
John Spencer wrote:

Can you post the actual code you are using?
Can you post the actual code you are using?

As far as I know if the form is open (not in design view) the code should
work. Does the form have a record source?

The problem could be that you are in the Not In List event or it could be some
type of corruption in the form.

I would try building a form bound to a table and then adding a button with
just the code in the click event to move to add new record. Does that work?
If so, continue experimenting.

Add a button to the existing form to add a new record? Does that work? If
not, perhaps you have a bit of corruption in the form?

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Ray C wrote:

On Monday, January 11, 2010 6:14 PM
Ray C wrote:

Thanks for coming back to me John.
Thanks for coming back to me John. The Form is definately not in Design view
and the code "Debug.Print FORMS.frm_Main.RecordSource" reports that
"tbl_Suppliers" is Bound to the Form "frm_Main".
I will try the procedures you suggest before I start posting my rather
"crappy" code for all to see and ridicule. Would you be able to pick up a
response in a few days?

thanks and regards Ray C

"John Spencer" wrote:

On Tuesday, January 12, 2010 8:25 AM
John Spencer wrote:

I may or may not be available.
I may or may not be available. You might consider starting a new thread with
a brief overview of the problem and what you have tried.

I usually pick up threads I am involved in, but sometimes I miss them.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Ray C wrote:


Submitted via EggHeadCafe - Software Developer Portal of Choice
WPF DataGrid as ComboBox Dropdown
http://www.eggheadcafe.com/tutorials/aspnet/e8585e81-34c8-4808-ae3e-b8b35d738842/wpf-datagrid-as-combobox.aspx
 | 
Pages: 1
Prev: Calculations in Group Footer
Next: counting in fields