Open a form with a specific record? 1

[Zerdax]

Hi i want to open a form with specific record.
It should popup a box asking me the value.

The field name is [dc code]
the form name is register form

any one can help me with the code for the command button

 

[earthandfire]

Set the criteria parameter of DoCmd.OpenForm to either:

---if dc code is numeric
"[dc code] = " & value"

---if dc code is text
[dc code] = '" & value & "'"

Hope this helps.

 

[Zerdax]

hi i got compile error.
DC code it text.

Would u mind writing the whole code.
the form name is [register form]

Thanks

 

[Zerdax]

iam using this.
Dim stDocName As String

stDocName = "Register form"
DoCmd.OpenForm stDocName, , , "[dc code] = '" & Value & "'"

But only getting in form. it isnt asking me the [dc code] i want to go to.

any help

 

[TheAceMan1]

Zerdax . . . . .

Try this:
[ol][li]Replace the code your using to open the form with the following:

[blue]   Dim Ans As String, Prompt As String, Title As String
   
   Prompt = "DC Code you want to go to?"
   Title = "DC Code?"
   Ans = InputBox(Prompt, Title)
   
   If InputBox(Prompt, Title) <> "" Then
      DoCmd.OpenForm "Register form", , , , , , Ans
   End If[/blue]

[/li]
[li]Then in the [blue]OnLoad[/blue] event of the opened form, copy/paste the following:

[blue]   Dim rst As DAO.Recordset
   Dim Msg As String, Style As Integer, Title As String
   
   Set rst = Me.RecordsetClone
   rst.FindFirst "[dc code]='" & Me.OpenArgs & "'"
   
   If rst.NoMatch Then
      Msg = "DC Code '" & Me.OpenArgs & "' NOT FOUND!"
      Style = vbInformation + vbOKOnly
      Title = "Can't find 'DC Code' Error! . . . . ."
      MsgBox Msg, Style, Title
   Else
      Me.Bookmark = rst.Bookmark
   End If
   
   Set rst = Nothing[/blue]

[/li][/ol]

See Ya! . . . . . .

 

[Zerdax]

I tryed but got compile error. User defined type not defined on:
Private Sub Form_Load()
Dim rst As DAO.Recordset

in onload form

When i click the button to search the dc code it asks me the dc code but for some reason 2 times and after that i get the error told above.

what can i do.

thanks

 

[PHV]

You have to reference the Microsoft DAO 3.x library.

 

[Zerdax]

and how do i do it?

Not that into access.

thanks

 

[TheAceMan1]

Zerdax . . . . .

The code requires [purple]Microsoft DAO 3.6 Object Library[/purple] to run. To [blue]check/install[/blue] the library, in any code window click [blue]Tools[/blue] - [blue]References...[/blue] In the listing find the library and [blue]make sure its checked.[/blue] Then using the up arrow, [purple]push it up as high in priority as it will go[/purple]. Click OK.

See Ya! . . . . . .

 

[Zerdax]

Hi. Thanks. It is working now.
Another thing i want. That if not found dc code and it should ask me if i want to register it and open the form inserted the dc code in the dc code field.

Thanks, and help

 

[TheAceMan1]

Zerdax . . . . .

First:

[blue][b]Change:[/b]
   If [purple][b]InputBox(Prompt, Title)[/b][/purple] <> "" Then
[b]To:[/b]
   If [purple][b]Ans[/b][/purple] <> "" Then[/blue]

Corrects Inputbox appearing twice.

New code for [blue]OnLoad[/blue] event:

[blue]   Dim rst As DAO.Recordset
   Dim Msg As String, Style As Integer, Title As String, DL As String
   
   DL = vbNewLine & vbNewLine
   Set rst = Me.RecordsetClone
   rst.FindFirst "[dc code]='" & Me.OpenArgs & "'"
   
   If rst.NoMatch Then
      Msg = "DC Code '" & Me.OpenArgs & "' NOT FOUND!" & DL & _
            "Do you want to register the DC Code?" & DL & _
            "Click 'Yes' to register." & DL & _
            "Click 'No' to abort . . ."
      Style = vbQuestion + vbYesNo
      Title = "Register DC Code ?"
      
      If MsgBox(Msg, Style, Title) = vbYes Then
         DoCmd.RunCommand acCmdRecordsGoToNew
         Me![purple][b]TextboxName[/b][/purple].DefaultValue = Me.OpenArgs
      End If
   Else
      Me.Bookmark = rst.Bookmark
   End If
   
   Set rst = Nothing[/blue]

See Ya! . . . . . .

 

[Zerdax]

Hi thanks.
It works but it doesnt write the dc code in the textbox DC_Code

First it asks the dc code. when it doesnt find it asks again if i want to register it. I press on yes. It opens the form but doesnt write the code i gave it to the Dc code.
Do i have to manualy re enter the code. Cant it just write it when i gave it the dc code to find.

this is the code in onload form

Dim rst As DAO.Recordset
Dim Msg As String, Style As Integer, Title As String, DL As String

DL = vbNewLine & vbNewLine
Set rst = Me.RecordsetClone
rst.FindFirst "[dc code]='" & Me.OpenArgs & "'"

If rst.NoMatch Then
Msg = "DC Code '" & Me.OpenArgs & "' NOT FOUND!" & DL & _
"Do you want to register the DC Code?" & DL & _
"Click 'Yes' to register." & DL & _
"Click 'No' to abort . . ."
Style = vbQuestion + vbYesNo
Title = "Register DC Code ?"

If MsgBox(Msg, Style, Title) = vbYes Then
DoCmd.RunCommand acCmdRecordsGoToNew
Me.DC_code.DefaultValue = Me.OpenArgs
End If
Else
Me.Bookmark = rst.Bookmark
End If

Set rst = Nothing

Help..

 

[Zerdax]

can u help me PHV or THEACEMAN1

 

[Zerdax]

Well at closer look, i saw that when u enter dc code in number it is shown then but if i write the dc code like this DC_002 then it shows #Name?
isnt this _ supported in the acces2002.
data type of the table is text.
any help

 

[TheAceMan1]

Try:

Something I over looked . . . . add the following [purple]purple[/purple] line where you see it:

[blue]   If Ans <> "" Then
      DoCmd.OpenForm "Register form", , , , , , Ans
      [purple][b]Forms![Register form].SetFocus[/b][/purple]
   End If[/blue]

See Ya! . . . . . .

 

[Zerdax]

Thanks
But it wont write this type of value Dc_002 in the field dc code. It does display #NAME?
Any adjustment that could be made. thanks for ur help...

 

[TheAceMan1]

Zerdax . . . . .

I believe [blue]Dc_002[/blue] is numeric, so:

[blue][b]Change:[/b]
   Me!TextboxName.DefaultValue = [purple][b]Me.OpenArgs[/b][/purple]
[b]To:[/b]
   Me!TextboxName.DefaultValue = [purple][b]Val([/b][/purple]Me.OpenArgs[purple][b])[/b][/purple][/blue]

See Ya! . . . . . .

 

[TheAceMan1]

Zerdax . . . . .

Apologies . . . my fault!. [blue]DefaultValue[/blue] has to be formatted properly (don't know how I forgot). Try:

[blue]   Me![[purple][b]TextboxName[/b][/purple]].DefaultValue = """" & Me.OpenArgs & """"[/blue]

See Ya! . . . . . .