Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Ms access add record to table via unbound text box Expand / Collapse
Author
Message
Posted Wednesday, October 24, 2012 10:00 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 19, 2013 8:08 PM
Points: 43, Visits: 566
Front end MM Access adp file.
On SQL server 2005

Form bound but user must not change data directly
Unbound text box to input new data
Save button to add record.

Forms!some form!text box can't append...

Tried Me.textbox won't work...

Any advice?
Post #1376758
Posted Thursday, October 25, 2012 4:59 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 4:34 PM
Points: 48, Visits: 9,470
Hi

The brevity of your post is impressive but is a little limiting when it comes to providing you with proper advice. So posting the code behind the Save button would have made things a whole lot clearer and if my response is not helpful in solving your problem, then I suggest you actually post your code.

However, I think I get the gist of what your problem is so, ignoring the obvious question as to why you would want to torture yourself in this way lets see what I can come up with.

To update the database manually the code behind the Save button will need to build and execute an SQL string. The simplest way to build you string is to simply use the Access Query function - but wait, you are using an ADP file (another obvious "why" question there) so I don't think that is going to work. You will need to include the control(s) from the form as the value(s) - me!ControlName should work unless the control is on a subform.

If you are doing record updates from this form, you will need to program both an append query and an update query (maybe use different buttons) - and then of course you might need to perform deletes in which case you will also need to build a delete button and appropriate code.

I hope this gets you started. If you have never created SQL strings in VB before, have a bit of a read and give it a go, but post back if you have trouble (post your code too) and I'll give you some tips on how to go about it.

Cheers

Rowan
Post #1377332
Posted Thursday, October 25, 2012 8:59 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 19, 2013 8:08 PM
Points: 43, Visits: 566
It is a brave ask...cause i searched all over and still cant come up with a solution.

I was thinking input parameters..but still havent got that to work fully yet.

I havent been able to create any codes for the button...

I do know a work around for the problem but it would be really great if I could find a way to do this still.

you seem to have the idea as to what i am trying to do. Its sort of like building a project in VB but in MS instead.




The thing is, how do i let the append stored procedure know that I am appending data FROM the form?
Post #1377367
Posted Thursday, October 25, 2012 10:58 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 4:34 PM
Points: 48, Visits: 9,470
Hi

The following code is an example of the Event Procedure assigned to the OnClick event of a button. It demonstrates how to create an SQL string and how to execute it. The pieces of text starting "me." are referencing controls on the form. If I was being fussy, I should be using "me!" to avoid any confusion in Access. This example is probably a bit more complex than you currently need as I am doing some stuff with some dates which are part of the record I'm inserting. I also have a loop for reasons which currently escape me (this is from a project I was working on a year ago).

It includes an example of both an insert and a delete - all you need now to complete the set is an update (and if you know SQL that shouldn't be too hard). But most importantly it provides you with an example of building an SQL string in VB. Positioning and inclusion of the double-quote characters (") are crucial as they not only mark the points at which the string is interrupted to add in the control values eg

& ",""" & Nz(Me.Brand, "") & """,""" &

but they are also used to surround string values.

The ampersand character (&) is the joiner but one of the most confusing things is the use of the double-quote. & """,""" & translates into the completed string as ",". Therefore & ",""" & Nz(Me.Brand, "") & """,""" & translates into ,"SomeBrandNameFromTheForm","

I suggest you do some research into using breakpoints and the Immediate Window (activated using <CTRL>G) if you don't already know how to use them. Its very handy to step through your code and to display the actual string being created.

The SetWarnings False command turns off the messages asking the user to confirm the action and SetWarnings True turns them back on again - a lot of new developers fail to turn them back on again and get into strife - you have been warned

I'm using the NZ function because most of the values I'm inserting are strings so to avoid any issues I'm turning NULLs into empty strings (habit from coding Transact SQL).

Hmm! Re-reading your last past you mentioned stored procedures - are you wanting to use a stored procedure to do the data stuff? If so, it is possible to do it, but then I am used to using the Access MDB format rather than ADP so I'm a little vague as whether or not the methods I would use in an MDB will still work in an ADP. Lets tackle that when I have more info.

Please note, I live in New Zealand so our time zones are likely out of synch. I'm making this last post from my desk at work and then heading home - it is coming up for 6pm. It is also Friday Wohooo!! TGIF!!! But I'll keep pace with this over the weekend when I get spaces.

All the best.

Cheers


Private Sub btnUpdateDataWarehouse_Click()
Dim dtEndOfMonth As Date
Dim dtLoopDate As Date
Dim strSQL As String

dtEndOfMonth = DateSerial(Year(Date), Month(Date), 0)
dtLoopDate = DateAdd("m", -12, dtEndOfMonth)

Do While dtLoopDate <= dtEndOfMonth
strSQL = "Insert Into dbo_rpt_ProgressiveStores ( " & _
"Store,[Site No],Brand,Region,Area,SiteDescription,SiteAddress, " & _
"SiteSuburb,[State],PostCode,TargetTonnes,DefaultDensity,ForTheMonthEnding) " & _
"Select " & _
"""" & Nz(Me.Store, "") & """," & Me.SiteNo & ",""" & Nz(Me.Brand, "") & """,""" & Nz(Me.Region, "") & """," & _
"" & Nz(Me.Area, "NULL") & ",""" & Nz(Me.SiteDescription, "") & """,""" & Nz(Me.SiteAddress, "") & """," & _
"""" & Nz(Me.SiteSuburb, "") & """,""" & Nz(Me.CState, "") & """,""" & Nz(Me.PostCode, "") & """," & _
Me.TargetTonnes & "," & Me.DefaultDensity & ",#" & dtLoopDate & "#;"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

dtLoopDate = DateSerial(Year(DateAdd("m", 1, dtLoopDate)), Month(DateAdd("m", 1, dtLoopDate)) + 1, 0)
Loop
MsgBox "New Store data created.", vbOKOnly, "Add Store Data"
strSQL = "DELETE ProgressiveStores.*, ProgressiveStores.[Site No] " & _
"FROM ProgressiveStores " & _
"WHERE (((ProgressiveStores.[Site No]) = " & Me.SiteNo & "));"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

Me.Form.Requery

End Sub



Post #1377391
Posted Sunday, October 28, 2012 8:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 19, 2013 8:08 PM
Points: 43, Visits: 566
Okay,

after thinking long and hard as to how I will go about doing this, I decided that I will use ADP file to creat stuff, stored procedures especially and use ACCDB files as user front end.

I will also use web browsers to return reports standard to employees who wish to review stuff..

Got the layout well mapped out!

Also, using the ACCDb files gives me the option to connect to oracle!

Great bridge!


Thanks for the assistance. It still bothers me though that I was unable to achieve this. I hate it when that happens! Guess it happened for a reason ;)
Post #1378028
Posted Sunday, October 28, 2012 11:14 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 4:34 PM
Points: 48, Visits: 9,470
Hi

I think you have made the right choice - once you have the frontend developed you can compile it as well. Using an ADP forces you down a certain path, one which I have always found to create more work and maintenance hassles than it was worth. And you are right about the flexibility of the ACCDB with Oracle - ODBC might not be the fastest connection method but it is relatively simple to work with - but then I have been using ODBC for quite sometime now so I'm biased.

You can still utilise stored procedures in the ACCDB format - you just have to execute them via pass-through queries. A nice way to overcome some of the downsides to the way Access handles data. Passing parameters means you are forced to utilise VBA and querydef objects, but they are not difficult and there are plenty of examples around should you need a prompt. You can always post here if you have specific issues.

Using stored procedures via pass-throughs to handle database inserts/updates etc is a bit more work to build but possibly one of the better ways to use Access as a frontend. You have to weigh that sort of design against the standard Access approaches, but if you are working with a lot of unbound forms, I think the sp track is worth the effort. I'm sure a thread on that topic would draw a variety of opinions

All the best with your development. I hope I was able to help in some manner.

Regards

Rowan
Post #1378101
Posted Wednesday, October 31, 2012 4:51 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 19, 2013 8:08 PM
Points: 43, Visits: 566
You have helped a lot actually. It was tru these discussions that i am talking to my self and realizing the best approach.

Anyways, i will continue to use this thread to post and I go along

so a few problems i encountered so far:
1. Bit field and boolean value ... work around, if you want to use a check box in an access form bound to a bit field in sql server...make sure that the bit field is set to yes/no.

2. all my data shows #deleted..fix...open linked table manager and refresh all tables...the connection was lost.

3. running my stored procedure...pass through queries work...but i dont think pass through queries like the DECLARE statement with the @ before the parameters...so what do you do..simple...do a pass through query with the statement being:

exec stored-procedure-name

set the ODBC Connect to the connection of the database.
if you are running an action query...set the "Return Records" property to No.

Yep, every day is one problem or another but at the end of the day, i learn...Thank goodness for google!
Post #1379562
Posted Wednesday, October 31, 2012 4:53 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 19, 2013 8:08 PM
Points: 43, Visits: 566
i'm currently using a thread in the ms access section to post my findings as i go along. I am sure that there are others who experience these problems every day!
Post #1379563
Posted Wednesday, October 31, 2012 5:26 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 19, 2013 8:08 PM
Points: 43, Visits: 566
problem: write conflict in ms access
solution...
make sure the table has a primary key
make sure no bit field has a null value
and ensure that the table has a timestamp field.
Post #1379566
Posted Friday, November 2, 2012 2:44 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 11, 2014 1:51 PM
Points: 100, Visits: 788
Sounds like the same advice you can find here: http://accessexperts.net/blog/2012/07/31/my-best-presentation-is-now-online-optimizing-access-with-sql-server/#comments

"i'm currently using a thread in the ms access section"
I can't see the thread, have you started it?
Post #1380199
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse