Append records from Subform (1 Viewer)

[Antony Cole]

Hi,

I have an append query which pulls records from a subform within a main form. Only trouble is it only pulls the first record. How do I pull all records?. Really stuck on this. Hope someone can help. In my criteria box my expression is [forms]![QuoteDetailsT]![SubContractorT Subfom1]![Quote Ref].I do have more columns but I guess you get the idea.

Regards

Antony Cole

 

[spikepl]

"Append query which pulls records from a subform".

I don't understand this - can you be more specific?

 

[Antony Cole]

Hi, thanks for replying. I have an append query
That appends records from a sub form. The problem
I have is it doesn't append all the records. It only appends the
First record to the destination table, and I don't know why?.
Any suggestions?

Thanks

 

[spikepl]

Uhmm, repeating the same information and just rearranging the words a little does not clarify the issue at all.

What does it mean "append records from a subform"? Do you on a subform have some code that from somewhere (where?) appends records to somewhere (where?). Or do you grab the current recordset of the subform and append that data to somewhere else (where?)?

 

[Antony Cole]

Ok let me try and explain. I have a quote table that
Contains customer info. I have a subcontractor table.
I have a materials table and a labour table. All of
Which are linked in a relationship via the quote ref and are part
Of a main form. I have a button that runs an append
Query that takes information from the subcontractor
Subform and puts it onto a new table. The subcontractor subform
Could contain more than one record but when I run the append
Query it only appends the first record on the subform. I need it to append
All of them?. Any ideas. If you would like me to forward my db to you
To look at if it would help I will

Regards

 

[Pat Hartman]

I am also confused by your terminology. Forms don't store records. Tables store records. If you are referencing form fields in a query - Forms!yourForm!yoursubform.Form.control1 - you will be referencing only the "current" record in the form. You are not referencing the table which holds all the records.

You need to change the append query to select records from the table using the same criteria to filter the data that the form is using.

 

[Antony Cole]

Re: Append records from Subform http://www.mediafire.com/?233yarazau7as4b

Hi,

ok. Here is a copy of my DB above. If you look at the QuoteDetailT form you will see a button at the top of the page that says "Create Job". This button runs an append query for the current record in the form, and an append query for a subform (Sub Contractor) for the current record in the subform. How do I create an append query to append all records in sub contractor relating to the current record in the form. Hope this makes sense. Sorry if my terminology is off, i'm not an expert.

regards

 

[Pat Hartman]

Please use this site's upload facility to upload your database. I don't download files from unknown sites.

 

[Antony Cole]

Hi, Please find attached DB

Regards

Antony

 

[Pat Hartman]

You have too many criteria fields in both queries. It doesn't hurt the parent record because you are copying only a single record (although you should still remove the extraneous fields) but you have criteria on every field of the child table that refers to the fields in the subform. Change the criteria for the subcontractors so that it references ONLY the primary key field from the main form.

 

[Antony Cole]

Reference text box from form to a query

Hi,

This seems pretty easy and have done it in the past, but i'm trying to reference a text box on a form in a normal query. The code I have is [Forms]![ProjectdetailsT]![Text154]. I just keep getting an "enter parameter value" box when I try to run my query. Iv'e tried pretty much everything I can think of to get it to work, even writing the reference in the Query parameter value box, still doesnt work. I have similar queries in my DB to this but they are append queries?. Please can someone help.

Thanks

Antony

 

[Pat Hartman]

When Access pops up a parameter box, it's because it can't find the field you referenced and so it assumes it is a parameter. Either the name is incorrect (fields named Text154 are poor practice BTW) or the form is not open. The form MUST be open if you want to reference it and remember you are only referencing the current record.

 

[Antony Cole]

Runtime error 3075 syntax error in string '[username=2]'

Hi,

I am trying to make a username and password form that logs you onto my database. I have a small form with an unbound combo box that you can select username and then underneath another text box to type a password. Underneath that I have a button which if the username and password values are correct takes you to the home screen. I have a problem with the D lookup part of my expression, I think i'm missing some quotes but i'm not sure. The unbound combo box has 2 columns ID, and Username but focuses on the username using the column widths. Please can someone help. expression string below.

Private Sub Command7_Click()
'Check to see if data is entered into the UserName combo box
If IsNull(Me.Username) Or Me.Username = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
Me.Username.SetFocus
Exit Sub
End If
'Check to see if data is entered into the password box
If IsNull(Me.Password) Or Me.Password = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Me.Password.SetFocus
Exit Sub
End If
'Check value of password in tblEmployees to see if this
'matches value chosen in combo box
If Me.Password.Value = DLookup("ID", "UsersT", _
"[Username]=" & Me.Username.Value & "'") Then
ID = Me.Username.Value
'Close logon form and open splash screen
DoCmd.Close acForm, "UsersForm", acSaveNo
DoCmd.OpenForm "Mainmenu"
Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
"Invalid Entry!"
Me.Password.SetFocus
End If
'If User Enters incorrect password 3 times database will shutdown
intLogonAttempts = intLogonAttempts + 1
If intLogonAttempts > 3 Then
MsgBox "You do not have access to this database.Please contact admin.", _
vbCritical, "Restricted Access!"
Application.Quit
End If

End Sub

 

[Pat Hartman]

You are missing a single quote before the username. Are you sure that password is called ID in UsersT?

If Me.Password = DLookup("ID", "UsersT", _
"[Username]= '" & Me.Username & "'") Then

 

[Antony Cole]

Hello again!,

I want to add another control element to my code above which has manager level so only certain users can access a specific menu. I have in the table an extra column which contains either User or Manager. I need to add a D lookup in there and a messsgae to say if anyone other than a manager tries to login they dont have the access but I'm not sure where I need to place the code or how it should be written. Please can you help?

thanks

Antony