Solved

How can I get a value from a Form by coding??

Posted on 2004-04-21
12
293 Views
Last Modified: 2006-11-17
Here is part of the coding:

Private Sub Edit_Click()

stDocName = "DataEntry_Staff_Edit"

stStaffCode = (How can I get a value from form?? let's say I would like to get the staff code from a text box "StaffCode" from a form "DataEntry_Staff")

DoCmd.OpenForm stDocName, acFormEdit, acWindowNormal

End Sub
0
Comment
Question by:ryan0107
  • 5
  • 4
  • 3
12 Comments
 
LVL 6

Accepted Solution

by:
nathaniel earned 500 total points
ID: 10885728
In Access2000 try these:

Private Sub Edit_Click()

  stDocName = "DataEntry_Staff_Edit"

  stStaffCode = [DataEntry_Staff]!StaffCode

  DoCmd.OpenForm stDocName, acFormEdit, acWindowNormal

End Sub
0
 

Author Comment

by:ryan0107
ID: 10885754
Sorry, then how can I use   DoCmd.OpenForm to open the stDocName and with filtering the stStaffCode??
0
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 10885756
Try this:

in a module add this:

Public Function ifIsLoaded(ByVal strFormName As String) As Integer
'Returns a 0 if form is not open or a -1 if Open
    If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> 0 Then
        If Forms(strFormName).CurrentView <> 0 Then
            ifIsLoaded = True
        End If
    End If
End Function

then to check the value, use this:

Private Sub Command1_Click()
    If ifIsLoaded("DataEntry_Staff_Edit") = 0 Then
        MsgBox "DataEntry_Staff_Edit Not loaded!"
    Else
        MsgBox Form_DataEntry_Staff_Edit.Controls("StaffCode").Value
    End If
End Sub
0
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 10885763
DoCmd.OpenForm formname[, view][, filtername][, wherecondition][, datamode][, windowmode][, openargs]

Example:

DoCmd.OpenForm "DataEntry_Staff_Edit", , ,"stStaffCode= '" & Form_DataEntry_Staff_Edit.Controls("StaffCode").Value & "'"
0
 
LVL 6

Expert Comment

by:nathaniel
ID: 10885814
to apply it as filter in opening form, you may try this:

CHANGE
DoCmd.OpenForm stDocName, acFormEdit, acWindowNormal

TO:

'if stStaffCode is of string type:
DoCmd.OpenForm stDocName,,,"stStaffCode=" & """" & stStaffCode & """", acFormEdit, acWindowNormal


'if stStaffCode is of number type:
DoCmd.OpenForm stDocName,,,"stStaffCode=" & stStaffCode, acFormEdit, acWindowNormal

hope this helps,
0
 

Author Comment

by:ryan0107
ID: 10885826
Sorry,  when I use this code:

DoCmd.OpenForm "DataEntry_Staff_Edit", , ,"stStaffCode= '" & Form_DataEntry_Staff_Edit.Controls("StaffCode").Value & "'"

An error message was displayed as follow:

"Run Time Error 3075
Syntax Error (missing operator) in query expression 'stStaffCode=1234'"

Can you help me to solve it??
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 49

Expert Comment

by:Ryan Chong
ID: 10885900
>>"Run Time Error 3075
Syntax Error (missing operator) in query expression 'stStaffCode=1234'"

is you missing something? >>'stStaffCode=1234'", this definitely is wrong! try:

"stStaffCode= '" & Form_DataEntry_Staff_Edit.Controls("StaffCode").Value & "'"

stStaffCode='1234'"

or

"stStaffCode= " & Form_DataEntry_Staff_Edit.Controls("StaffCode").Value

stStaffCode=1234"
0
 

Author Comment

by:ryan0107
ID: 10885951
Basically,  here is the code:

Private Sub Edit_Click()
stDocName = "DataEntry_Staff_Edit"
stStaffCode = [Forms]![DataEntry_Staff]![Staff Code]
DoCmd.OpenForm stDocName, , , "Staff Code= " & stStaffCode, acFormEdit

End Sub

An error message shown as :

>>Run Time Error 3075
Syntax Error (missing operator) in query expression 'Staff Code=1234'

Any idea can give me??
0
 
LVL 6

Expert Comment

by:nathaniel
ID: 10886042
We'll start with this first:

is the stStaffCode a number type of string type?
0
 
LVL 6

Expert Comment

by:nathaniel
ID: 10886098
We'll start with this first:

is the stStaffCode a number type or string type?

if string type, use:

DoCmd.OpenForm stDocName, , , "Staff Code= " & """" & stStaffCode & """", acFormEdit

if not, use:

DoCmd.OpenForm stDocName, , , "Staff Code= " & stStaffCode, acFormEdit

0
 

Author Comment

by:ryan0107
ID: 10887194
Thanks.  I can open the form successfully... but can't filter just that staffcode record from my table.  All records were extracted from a table just as usual.

Basically,  I want to use above codes to open a new form with just one related profile (making use of Staff Code) and then go right ahead for edit.

Do you think my coding is right??

Sorry I have too many questions coz I'm just new to Access
0
 
LVL 6

Expert Comment

by:nathaniel
ID: 10887402
That's ok ryan don't give up!

If you can open the form successfully, then we now need to fix the condition part.

try this one first:

msgbox stStaffCode
DoCmd.OpenForm stDocName, , , "[Staff Code] = " & stStaffCode, acFormEdit

explanation:
the msgbox.. will let you know if you are getting a value

let me know if it works,



0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now