Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2004-04-21
12
Medium Priority
?
341 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 2000 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 55

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 55

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
 
LVL 55

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Implementing simple internal controls in the Microsoft Access application.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

564 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