Form pulling data from two locations and view data

[sovereign]

Hi,

I am relatively new working in VBA and SQL,

I have created a form called "Frmfleetchecksheet"

1st thing: I have a search box that points to "qryFrmfleetchecksheet" on update that then populates 3 fields by using record source and control source

At the moment it has multiple fields around 20 that I have code that inserts these into a table "fleet_checksheet"

i then wanted to link the fields to this table so i can pull previous checksheets. (this will be done via a button from another form)

but if i add both tables into "qryFrmfleetchecksheet" i get Recordset is not updateable

I have checked.. data entry to yes, recordset type on qry is Dynaset, unique values is set to no.

I think i also need the qry to show all from both tables is the possible?



not sure if its best to use some code to populate the 3 fields on a 'search' button click for example from the table and insert into the form rather than using a qry.

Any help would be much appreciated and thanks in advanced

Regards

[ranman256]

Even if it is a dynaset , SOME queries, due to joining 2 tables, cannot update.
That join prevents update.
usu because they don't have a true 1 to many relation.

[sovereign]

i see, so if i wanted to pull data direct from the table "fleet"

Private Sub Command913_Click()

Dim Reg As String
Dim Fireg As String
Dim Fimake As String
Dim Fimodel As String

Reg = Me.txtFregSearch

Fireg = DLookup([Freg], [Fleet], [Reg])
Fimake = DLookup([fmake], [Fleet], [Reg])
Fimodel = DLookup([Fmodel], [Fleet], [Reg])

Me.txtFReg.Value = Fireg
Me.txtFmake.Value = Fimake
Me.txtFmodel.Value = Fimodel

End Sub

I have got this but says it cannot find the Field 'Freg' but this is what it is called

[Missinglinq]

First off, is the Field in question, that Access cannot find, actually Freg, as you currently have, or is it Freq? If the latter, you'll need to correct all instances of it in the code.

Also, your syntax for the DLookups isn't correct. The syntax varies depending on the DataType of the Field in the Where Clause. For your first example it would be:

If Where Field is Numeric

Fireg = DLookup("Freg", "Fleet", "[MatchingFieldInTable] = " & Me.txtFregSearch)

If Where Field is Text

Fireg = DLookup("Freg", "Fleet", "[MatchingFieldInTable] = '" & Me.txtFregSearch & "'")

where MatchingFieldInTable is the Field in the Fleet Table that needs to match Me.txtFregSearch.

Note that the Field to be returned has to be enclosed in Quotation Marks.

Linq ;0)>

[sovereign]

Thank you for your detailed response it is much appreciated!

will have a play with it now. it should be "Freg"

[sovereign]

it worked perfectly! Thank you so much

[Missinglinq]

Glad we could help!



Welcome to AFN!

Linq ;0)>